Need formula to Detect "Different products" for same ID IN EXCEL

Question

I'll need a formula in excel to detect for example 1111; has three different products linked, meanwhile 2222 has only linked "oranges"

Here I left and example of my excel table:

Product ID	Product Name
1111	Apples
1111	Orange
1111	Grapes
2222	Orange
3333	Apples
3333	Orange
3333	Grapes
4444	Orange

Hope it is clear!

Thanks in advance.

If i concatenate eg: 1111- Apple, 1111-Orange, 1111-Grapes and then i do a countIf it will throw a 1 in each row, Ill need a formula to give me the info that "product id" has 3,4....n products linked to it. Sry if i dont explain myself correctly. — JoseCJ, Mar 10 '22 at 16:17
@JoseCJ Try using this formula, it shall give you the expected output `=COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"&B2)+1` Assuming `A1` & `B1` is header, so place the formula in cell `C2` — Mayukh Bhattacharya, Mar 10 '22 at 16:24
If column B contains unique values a count if on column A only would suffice even. — P.b, Mar 10 '22 at 16:34
@JoseCJ just to make it look fancy you may use this as well, `="Product ID "&A2&"-Product Type No.-"&COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"&B2)+1` — Mayukh Bhattacharya, Mar 10 '22 at 16:39
Thx @MayukhBhattacharya trying i think its working must do some testing as B has duplicates values too, i explain my self i could have several 1111 - apples row. — JoseCJ, Mar 10 '22 at 16:39
@JoseCJ so did you try the one as suggested ? Or do you need a screenshot for the example! — Mayukh Bhattacharya, Mar 10 '22 at 16:41

score 0 · Accepted Answer · answered Mar 10 '22 at 16:46

0

Sharing just for clarity, refer image below,

• Formula used in cell C2

=COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"&B2)+1

• Formula used in cell D2 --> You may use this as well or modify to make it look bit fancy

="Product ID "&A2&"-Product Type No.-"&COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"&B2)+1

answered Mar 10 '22 at 16:46

Mayukh Bhattacharya

12,541
5
21
32

1

Thanks it actually does! :) – JoseCJ Mar 10 '22 at 16:51
@JoseCJ Glad you are able to work it out, thank you for sharing the feedback as well! – Mayukh Bhattacharya Mar 10 '22 at 16:52
it seems when i have a pair repeated, EG 1111- apple 2/3 times it does not gives consistent info. The final objective is to identify which "product IDs" have 1 product associated and which one have more than 1 product associated. If i have two times 1111- apples it will have only 1 product even the same row is repeated, sry if im not explaining myself .... :( – JoseCJ Mar 10 '22 at 17:28
@JoseCJ so you mean to say if there is 3 1111 & 3 apple then it would show 1 for once and the rest will be empty or like it should show as 1,2,3? – Mayukh Bhattacharya Mar 10 '22 at 17:34
The first one, like you said if 1111+appple is reapeated three times i want to show 1, and if then a new row contains 1111+orange then this new one shows a 2 instead. – JoseCJ Mar 10 '22 at 17:38
@JoseCJ alright understood now give me a moment sharing soon – Mayukh Bhattacharya Mar 10 '22 at 17:58
tons of thanks ! – JoseCJ Mar 10 '22 at 18:07
@JoseCJ try this `=IF(COUNTIF($A$2:A2,A2)=1,1,IF(B2=B1,C1,C1+1))` – Mayukh Bhattacharya Mar 10 '22 at 20:41
C column cell should contain this formula right ? =COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"&B2)+1 – JoseCJ Mar 10 '22 at 21:22
@JoseCJ No, use the new one, also note the new formula works when the range is a sorted one.! – Mayukh Bhattacharya Mar 10 '22 at 21:26
when using this one my "C COLUMN" is empty I just have A - Product Id / B - Product name =IF(COUNTIF($A$2:A2,A2)=1,1,IF(B2=B1,C1,C1+1)) – JoseCJ Mar 10 '22 at 21:31
@JoseCJ can you share a onedrive link here in comments of your excel workbook? – Mayukh Bhattacharya Mar 10 '22 at 21:37
i was idiot missing a "." in formula. All working know! – JoseCJ Mar 10 '22 at 21:49
@JoseCJ thank you very much for sharing the feedback sir! – Mayukh Bhattacharya Mar 10 '22 at 21:51
1

Any way to upvote or something? Almost first time here. Much appreciate your time & your solution! – JoseCJ Mar 10 '22 at 21:58
@JoseCJ its okay, you cant upvote now, unless you have 15 rep, so you have already accepted the same an answer, thats fine and thank you! – Mayukh Bhattacharya Mar 10 '22 at 22:21

Need formula to Detect "Different products" for same ID IN EXCEL

1 Answers1