Handling Duplicate Values

[mskusace]

I have a lookup table that looks like the table below. It is truncated and masked to simplify the problem, but the logic will be the same.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]CODE[/TD]
[TD]REASSIGN(CODE)[/TD]
[/TR]
[TR]
[TD]W12-05[/TD]
[TD]AA[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]W12-05[/TD]
[TD]BB[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]W14-01[/TD]
[TD]CC[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]W14-03[/TD]
[TD]AA[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]W15-01[/TD]
[TD]DD[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]W15-01[/TD]
[TD]DD[/TD]
[TD]DD[/TD]
[/TR]
</tbody>[/TABLE]

ID and CODE are generated from the report
REASSIGN(CODE) is what I am trying to accomplish. If there are multiple IDs (ex. W12-05) that are the same with different CODE values, then assign it to "ZZ" . However, if there are multiple IDs and they all have the same CODE values (ex. W15-01), then assign it to the same code value.

My other data set has ID with some other values, but I need to use the lookup table to get the REASSIGN(CODE).

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]LOOKUP REASSIGN(CODE)[/TD]
[/TR]
[TR]
[TD]W12-05[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]W14-01[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]W14-03[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]W15-01[/TD]
[TD]DD[/TD]
[/TR]
</tbody>[/TABLE]

This way, even if there are multiple instances of an ID, as long as each ID has the same code the VLOOKUP will pull the correct value no matter which one it selects.

How can I accomplish the REASSIGN(CODE) column in the first table? Something along the lines of:
If IDs duplicate and multiple different CODEs, assign to ZZ.
If IDs duplicate and same codes, assign to same CODE.

There might be instances where if there certain multiple codes for duplicate IDs, it will need to assign to a specific CODE. "ZZ" is the default when I don't know where it should go and it can be handled by another department. Sometimes I know that code "EE" and code "FF" belong to one department and there is logic to assign it to "GG" for example.

I am open to suggestions and ideas, even if much different than my presented solution. Any assistance would be greatly appreciated.

Please let me know if you need any clarification!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C
1	ID	CODE	REASSIGN(CODE)
2	W12-05	AA	ZZ
3	W12-05	BB	ZZ
4	W14-01	CC	CC
5	W14-03	AA	AA
6	W15-01	DD	DD
7	W15-01	DD	DD
A1

Cell Formulas
Range	Formula
C2	=IF(COUNTIF($A$2:$A$7,A2)<>COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,B2),"ZZ",B2)

 

[Marcelo Branco]

Maybe something like this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

ID​

[/td][td]

CODE​

[/td][td]

REASSIGN(CODE)​

[/td][td][/td][td]

ID​

[/td][td]

LOOKUP REASSIGN(CODE)​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

W12-05​

[/td][td]

AA​

[/td][td]

ZZ​

[/td][td][/td][td]

W12-05​

[/td][td]

ZZ​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

W12-05​

[/td][td]

BB​

[/td][td]

ZZ​

[/td][td][/td][td]

W14-01​

[/td][td]

CC​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

W14-01​

[/td][td]

CC​

[/td][td]

CC​

[/td][td][/td][td]

W14-03​

[/td][td]

AA​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

W14-03​

[/td][td]

AA​

[/td][td]

AA​

[/td][td][/td][td]

W15-01​

[/td][td]

DD​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

W15-01​

[/td][td]

DD​

[/td][td]

DD​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

W15-01​

[/td][td]

DD​

[/td][td]

DD​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in C2 copied down
=IF(COUNTIF(A:A,A2)<>COUNTIFS(A:A,A2,B:B,B2),"ZZ",VLOOKUP(A2,E:F,2,0))

Hope this helps

M.

 

[mskusace]

Thank you both for the replies. It appears to be working in most cases; however, I am getting some results as "0". I am not sure where the zero is showing up or why. Any possible reasons?

 

[mskusace]

It looks like Excel isn't updating the formula. I have automatic calculation on and 222,465 rows of data. I even copied the data and pasted it as values into a new spreadsheet to eliminate any other calculations. I have to double click each cell and then select enter. Am I exceeding my computer and Excel's calculation power? Should I look at SQL for this? If so, any recommendations on how to convert that to a SQL query?

 

[Fluff]

For that amount of data it will probably take a bit of time to calculate.
I know nothing about SQL or Power BI, so don't know if they would be better