# Counting Unique Record

#### Karan001

##### Board Regular
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.

 Name Company CAR1 Transport-01 CAR1 Transport-01 ZEEP1 Transport-01 ZEEP1 Transport-01 CAR2 Transport-01 CAR2 Transport-01 B1 Transport-01 B1 Transport-01 B2 Transport-01 B2 Transport-01 CAR3 Transport-02 CAR3 Transport-02 ZEEP2 Transport-02 ZEEP2 Transport-02 CAR4 Transport-02 CAR4 Transport-02 CAR5 Transport-02 CAR5 Transport-02

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

RESULT :

 Company Name Total Transport-01 CAR 2 Transport-01 ZEEP 1 Transport-01 B 2 Transport-02 CAR 3 Transport-02 ZEEP 1

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

Regards,
Kavvya

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### vogel997

##### Active Member
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?

 Company Name Total Transport-01 CAR 2 Transport-01 ZEEP 1 Transport-01 B 2 Transport-02 CAR 3 Transport-02 ZEEP 1

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

#### Karan001

##### Board Regular
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
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

##### MrExcel MVP

Did you also look at the Insert | PivotTable option?

#### circledchicken

##### Well-known Member
Did you also look at the Insert | PivotTable option?

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

##### MrExcel MVP

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.

 Name Company Name Company Count CAR1 Transport-01 CAR Transport-01 2 CAR1 Transport-01 ZEEP Transport-01 1 ZEEP1 Transport-01 B Transport-01 2 ZEEP1 Transport-01 CAR Transport-02 3 CAR2 Transport-01 ZEEP Transport-02 1 CAR2 Transport-01 B1 Transport-01 B1 Transport-01 B2 Transport-01 B2 Transport-01 CAR3 Transport-02 CAR3 Transport-02 ZEEP2 Transport-02 ZEEP2 Transport-02 CAR4 Transport-02 CAR4 Transport-02 CAR5 Transport-02 CAR5 Transport-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))
``````

#### circledchicken

##### Well-known Member
Right. It's a challenge.
Haha!

I'm not sure I would use this formula approach for a problem like this, but as always those are some serious formulas you've devised!

#### Fazza

##### MrExcel MVP
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
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?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,811
Messages
5,855,775
Members
431,763
Latest member
AlexisChch

### 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.

### Which adblocker are you using?

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

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