Counting Unique Record

Karan001

Board Regular
Joined
Jul 22, 2009
Messages
113
Hi Experts,
I have the below given two column.And I would like to have result as shown under the title RESULT.Could you please provide me some Macro or any formula to achieve this.

NameCompany
CAR1Transport-01
CAR1Transport-01
ZEEP1Transport-01
ZEEP1Transport-01
CAR2Transport-01
CAR2Transport-01
B1Transport-01
B1Transport-01
B2Transport-01
B2Transport-01
CAR3Transport-02
CAR3Transport-02
ZEEP2Transport-02
ZEEP2Transport-02
CAR4Transport-02
CAR4Transport-02
CAR5Transport-02
CAR5Transport-02

<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>



RESULT :


CompanyNameTotal
Transport-01CAR2
Transport-01ZEEP1
Transport-01B2
Transport-02CAR3
Transport-02ZEEP1

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>


Regards,
Kavvya
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

vogel997

Active Member
Joined
Jan 22, 2010
Messages
412
Maybe this helps

Formula for the total column in "C27"

=SUMPRODUCT(--(A27=$B$2:$B$19),--(B27=LEFT($A$2:$A$19,LEN(B27))))/(COUNTA($A$2:$A$19)/SUMPRODUCT(1/COUNTIF($A$2:$A$19,$A$2:$A$19)))

Why are the double entries counted as one?

CompanyNameTotal
Transport-01CAR2
Transport-01ZEEP1
Transport-01B2
Transport-02CAR3
Transport-02ZEEP1

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Karan001

Board Regular
Joined
Jul 22, 2009
Messages
113
Hi vogel,
Thanks for your quick reply.Checked and working fine.Actually Double record i need to group as one for making summary.

Regards,
Kavvya
 

vogel997

Active Member
Joined
Jan 22, 2010
Messages
412
You're welcome,
in this case you can make the formula shorter.


=SUMPRODUCT(--(A27=$B$2:$B$19),--(B27=LEFT($A$2:$A$19,LEN(B27))))/2
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Did you also look at the Insert | PivotTable option?
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Did you also look at the Insert | PivotTable option?
Hi Aladin,

I think the PivotTable approach is possible, but in this specific case there would need to be some additional steps to configure the data first.

For example, one way might be:
-> Add an 'Adjusted Name' column to the original dataset, with a formula to exclude the numeric values at the end of each name
-> For versions older than Excel 2013 (where a Distinct Count option is natively unavailable in a PivotTable), add a manual 'Distinct count' indicator column, with a formula that inserts a one for the first occurrence of each distinct name/company combination
-> Then build the PivotTable, using the additional columns as appropriate
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Hi Aladin,

I think the PivotTable approach is possible, but in this specific case there would need to be some additional steps to configure the data first.

For example, one way might be:
-> Add an 'Adjusted Name' column to the original dataset, with a formula to exclude the numeric values at the end of each name
-> For versions older than Excel 2013 (where a Distinct Count option is natively unavailable in a PivotTable), add a manual 'Distinct count' indicator column, with a formula that inserts a one for the first occurrence of each distinct name/company combination
-> Then build the PivotTable, using the additional columns as appropriate

Right. It's a challenge.

NameCompany NameCompanyCount
CAR1Transport-01 CARTransport-012
CAR1Transport-01 ZEEPTransport-011
ZEEP1Transport-01 BTransport-012
ZEEP1Transport-01 CARTransport-023
CAR2Transport-01 ZEEPTransport-021
CAR2Transport-01
B1Transport-01
B1Transport-01
B2Transport-01
B2Transport-01
CAR3Transport-02
CAR3Transport-02
ZEEP2Transport-02
ZEEP2Transport-02
CAR4Transport-02
CAR4Transport-02
CAR5Transport-02
CAR5Transport-02

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4465" width=126><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3299" width=93><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5176" width=146><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2702" width=76><TBODY>
</TBODY>

E2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX(REPLACE($A$2:$A$19,LEN($A$2:$A$19),1,""),
  SMALL(IF(FREQUENCY(IF(($A$2:$A$19<>"")*($A$2:$A$19<>""),
  MATCH(REPLACE($A$2:$A$19,LEN($A$2:$A$19),1,"")&$B$2:$B$19,
  REPLACE($A$2:$A$19,LEN($A$2:$A$19),1,"")&$B$2:$B$19,0)),
  ROW($A$2:$A$19)-ROW($A$2)+1),ROW($A$2:$A$19)-ROW($A$2)+1),
  ROWS(E$2:E2))),"")

F2, control+shift+enter and copy down:
Rich (BB code):
=IFERROR(INDEX($B$2:$B$19,
  SMALL(IF(FREQUENCY(IF(($A$2:$A$19<>"")*($A$2:$A$19<>""),
  MATCH(REPLACE($A$2:$A$19,LEN($A$2:$A$19),1,""&$B$2:$B$19,
  REPLACE($A$2:$A$19,LEN($A$2:$A$19),1,"")&$B$2:$B$19,0)),
  ROW($A$2:$A$19)-ROW($A$2)+1),ROW($A$2:$A$19)-ROW($A$2)+1),
  ROWS(F$2:F2))),"")

G2, control+shift+enter and copy down:
Rich (BB code):
=SUM(IF(FREQUENCY(IF($B$2:$B$19=$F2,IF(REPLACE($A$2:$A$19,
  LEN($A$2:$A$19),1,"")=$E2,MATCH($A$2:$A$19,$A$2:$A$19,0))),
  ROW($B$2:$B$19)-ROW($B$2)+1),1))
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I like a pivot table solution - so not formula or code as requested, but especially good if you have a large amount of data. A nearly identical solution is via a query table, it would look just like your posted target solution whereas a pivot table looks a little different.

I've relied on the Names being of the form whateverN where N is from 1 to 9. And have assumed - though it isn't necessary - the source data has a normal defined named YourData.

Save the file. ALT-D-D-N & follow the wizard to the end choosing the option to edit in MS Query. Via the SQL button change the SQL to below, via the 'open door' icon exit MS Query.

Code:
SELECT A.Company, LEFT(A.Name,LEN(A.Name)-1) AS [Name]
FROM (SELECT DISTINCT B.Company, B.Name
FROM YourData B) A

regards

PS. I believe this should work in all versions of Excel with pivot tables. So Excel 95 on.
 
Last edited:

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi Fazza,

That is nice solution!

As an aside, any idea of a good way to deal with values of N outside your 1-9 range in a purely SQL solution?
I'm assuming you would have to write a seperate custom SQL function, but I'm not sure this is possible with MS Query?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,314
Members
414,441
Latest member
KellyTheKid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top