Q. How to count unique values with the same prefix (integer) concatenated with different text values, display the unique records ?

[Sharmad]

Q. How to count unique values with the same prefix (integer) concatenated with different text values, display the unique records, count of unique records and count of records with unique prefix integer but different txt

Example: Below table of system users have unique User ID: 43871Wally, 43871Daniel, 43871Craig - All these users have the same prefix 43871. And There are few duplicate entries with the same prefix 33871.
Note: There is no space in the original data between Integer and Name combines forms UserID.

Output Required: A) 43871Wally are unique entries should be populated in a separate column B) unique count of these entries should be calculated = 3 C) total count of entries "43871" should be calculated = 5
43871Daniel
43871Craig

User ID

43871Wally

43871Wally

43871Wally

43872David

43873Michael

43871Daniel

43871Craig

43872Paul

 

[Saba Sabaratnam]

Hi Sharmad,

-create Helper Column 1 using = LEFT(A2,5)
-create Helper Column 2 using =TRIM(MID(A2,6,4))
-enter the array formula =IFERROR(INDEX($C$2:$C$17,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$17),0)),"") by pressing Shift + Control + Enter in E2 and drag it down-
-enter =IF(E2<>"",COUNTIFS(C:C,E2),"") in F2
-enter the array formula =IFERROR(INDEX($B$2:$B$17,MATCH(0,COUNTIF($H$1:H1,$B$2:$B$17),0)),"")by pressing Shift + Control + Enter in H2 and drag it down-
-enter =IF(E2<>"",COUNTIFS(C:C,E2),"") in I2

Kind regards

Saba

 

[Sharmad]

Hi Sharmad,

-create Helper Column 1 using = LEFT(A2,5)
-create Helper Column 2 using =TRIM(MID(A2,6,4))
-enter the array formula =IFERROR(INDEX($C$2:$C$17,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$17),0)),"") by pressing Shift + Control + Enter in E2 and drag it down-
-enter =IF(E2<>"",COUNTIFS(C:C,E2),"") in F2
-enter the array formula =IFERROR(INDEX($B$2:$B$17,MATCH(0,COUNTIF($H$1:H1,$B$2:$B$17),0)),"")by pressing Shift + Control + Enter in H2 and drag it down-
-enter =IF(E2<>"",COUNTIFS(C:C,E2),"") in I2



View attachment 1603

Kind regards

Saba

 

[Sharmad]

Can you share the .Xls attachment, please. I am stuck with step3 - not able to drag the formula.

 

[Sharmad]

Thanks in advance at least I was able to do understand TRIM, LEFT function.

 

[Saba Sabaratnam]

Hi Sharmad,

We are not allowed to attach / load Xl files in this forum.

What I meant was that you have to copy the formula in E2 and paste it into 20 to 30 cells below E2 in the same column E,

You can see how these formulas appear after you copy it.

Kind regards

Saba

 

[Peter_SSs]

We are not allowed to attach / load Xl files in this forum.

But you could use XL2BB?