Excel Function to Extract Text String, Remove Duplicate & Count

[arijitirf]

Hi!
My excel sheet has two column having data like the enclosed image;

I need end result by showing count of unique item against each name in Col B.

First, formula will extract text string before number by removing "-" like below

Prime Incorporation
Swiss Automation
MSTS - TRS Corp.
Prime Incorporation
MSTS - TRS Corp.

Then it will remove the duplicate and return below;

Prime Incorporation
Swiss Automation
MSTS - TRS Corp.

Post that it will give us count against name like below:

Rajib: 2
Pulok: 1

Is that possible using Excel Function.

Required urgent help.

 

[HighAndWilder]

Hi!
My excel sheet has two column having data like the enclosed image;

I need end result by showing count of unique item against each name in Col B.

First, formula will extract text string before number by removing "-" like below

Prime Incorporation
Swiss Automation
MSTS - TRS Corp.
Prime Incorporation
MSTS - TRS Corp.

Then it will remove the duplicate and return below;

Prime Incorporation
Swiss Automation
MSTS - TRS Corp.

Post that it will give us count against name like below:

Rajib: 2
Pulok: 1

Is that possible using Excel Function.

Required urgent help.

Are you still using Excel 2016?

 

[arijitirf]

Are you still using Excel 2016?

Yes.

 

[arijitirf]

Yes.

I have got the solution by searching net. Hence, the thread is closed

 

[Fluff]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[arijitirf]

Thank you Fluff for correcting me.

I am delighted to post the solution here to help others on the subject mentioned topic.

Basically, I need count of Unique Company Name as mentioned in Column B against each Name as per Column C. Let assume I have narration Data in Range "B1:B200" and Name in Range "C1:C200". However, there are 7 Unique Names can be obtained if deduplication is executed between "C1:C200"

For the Unique Name to be filtered out from Column A, need a Function to extract data from left till the Numerics (Like if the narration is "Prime Incorporation - 2345 - Lunch" then the output will be "Prime Incorporation")

Function that has been used in the above scenario is appended below;

First Step (to find the Position of the Numeric String inside the Narration):
Using below function to determine the position of first number in the numeric string. Here "B1" is referred to the narration i.e. "Prime Incorporation - 2345 - Lunch"

Assume below Function is written at "D1"

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B1&"0123456789"))

Output will be generated as 23 at "D1"as the number starts at Position 23 from Left.

Next Step (to Extract Text String from Left removing the extra Space & "-" used in between Text and Number):
Now, using below Function to extract text before numeric i.e. "Prime Incorporation"

Assume below Function is written at "E1"

=IFERROR(LEFT(B1,D1-3),"")

Output will be generated as "Prime Incorporation" at "E1". Here in the above Function "B1" is referred to the narration i.e. "Prime Incorporation - 2345 - Lunch" and "D1" is referred to the position i.e. "23" and "-3" is used not to show the extra space & "-" used between Text String and Number String inside the narration.

Next Step (to Remove Duplicate Narration Extract and Count against each Name):

Now, as the last part of the entire process, below Function is used to Count Unique Text String at "E1" against Name at Column "C" assuming you have Output Data in Range "E1:E200" and count against at "J1"

Assuming below Function is written at "K1"

=SUM(--(LEN(UNIQUE(FILTER($E$1:$E$200,($C$1:$C$200=J1),"")))>0))

Output will be generated basis the unique count in Range "E1:E200" against the Name in "J1". Dragging the Function against the 7 Unique Name at "J1:J7" can give Unique Count as per the Output in Range "E1:E200" basis date in the concerned Range i.e. "J1:J7"

Hope this may help future readers or candidate having query on the similar topic.