Handling Duplicate Values

mskusace

New Member
Joined
Jan 2, 2019
Messages
18
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.

IDCODEREASSIGN(CODE)
W12-05AAZZ
W12-05BBZZ
W14-01CCCC
W14-03AAAA
W15-01DDDD
W15-01DDDD

<tbody>
</tbody>

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

IDLOOKUP REASSIGN(CODE)
W12-05ZZ
W14-01CC
W14-03AA
W15-01DD

<tbody>
</tbody>

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,112
Office Version
365
Platform
Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">ID</td><td style=";">CODE</td><td style=";">REASSIGN(CODE)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">W12-05</td><td style=";">AA</td><td style=";">ZZ</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">W12-05</td><td style=";">BB</td><td style=";">ZZ</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">W14-01</td><td style=";">CC</td><td style=";">CC</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">W14-03</td><td style=";">AA</td><td style=";">AA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">W15-01</td><td style=";">DD</td><td style=";">DD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">W15-01</td><td style=";">DD</td><td style=";">DD</td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$2:$A$7,A2</font>)<>COUNTIFS(<font color="Red">$A$2:$A$7,A2,$B$2:$B$7,B2</font>),"ZZ",B2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
Maybe something like this...


A
B
C
D
E
F
1
ID​
CODE​
REASSIGN(CODE)​
ID​
LOOKUP REASSIGN(CODE)​
2
W12-05​
AA​
ZZ​
W12-05​
ZZ​
3
W12-05​
BB​
ZZ​
W14-01​
CC​
4
W14-01​
CC​
CC​
W14-03​
AA​
5
W14-03​
AA​
AA​
W15-01​
DD​
6
W15-01​
DD​
DD​
7
W15-01​
DD​
DD​
8

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

New Member
Joined
Jan 2, 2019
Messages
18
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

New Member
Joined
Jan 2, 2019
Messages
18
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,112
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top