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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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
Back
Top