[SOLVED] Compare 2 colums of text and sum total

[sirholm]

Hi.

I've looked through numerous threads and haven't found the same subject as mine.

I have two columns of text where some are the same entries.

Column A:
NOK1110ND
GARNUVI255WT
TTONEXLBLACK
NOK1616BLCKND
TTONEXLBLACK
W100IBLACKND
WT13IBLACKND

Column B:
GARNUVI255WT
TTONEXLBLACK
NOK1616BLCKND
TTSTARTXXL
NOKC3TSILVND
NOKC5GREY
GARFOR305

And I would like a third column where it could display a "clean" list where it compared the text in column a+b and would give me a list where it removes duplicates from column B. If there are for example 2x TTONEXLBLACK in column A and there is 1xTTONEXLBLACK in column B, so it would display 1xTTONEXLBLACK in column C. I only need end data from column A, since the extras from column B is not being used.

The sum of this list should be:
NOK1110ND
TTONEXLBLACK
W100IBLACKND
WT13IBLACKND

The lists I'm comparing is maximum 1000 entries.

The last thing that would be perfect to have after this list is generated, if it could lookup in another sheet, find the same name-entry from column C5 to C300 and take the background color of this and display it in column C.

Other sheet (where it has the lookup):
WT13IBLACKND is marked as red background color. the other 2 from the "clean list" is not on the other list and should stay as they are.

So the last edited list should be

NOK1110ND
TTONEXLBLACK
W100IBLACKND
WT13IBLACKND (with red background color)

Got any idea how to solve it?

Thanks in advance

[sirholm]

Can anyone help with the first part only or some of it? I'm pretty stuck and I have to generate this data 30 times every week and I feel my life passing away, since I know it should be possible to make it automatic, at least some part of it

[karolus]

Hello
A three step routine:

1. Formula in C1:

[sirholm]

Thanks for your reply, but when I use:

Code:
=IF(COUNTIF($A$1:$A$1000;A1)>COUNTIF($B$1:$B$1000;A1;"")

in C1, it gives me error 504.
a1+b1+c1
NOK1110ND LGGD510BLACK Err:504

and tried the next, but it obviusly doesn't work, so got a clue what makes the error?

Thanks in advance

[karolus]

Hello

Sorry my fault:
Correctet:

[sirholm]

Thanks, now it doesn't give errors, but the data isn't correct in C-column.

When TTONEXLBLACK is shows twice in column A and only once in column B, it should only show TTONEXLBLACK once in column C.

GARNUVI255WT is shown once in column A and B and therefore it should not be shown in column C.

Sorry if I'm not clearly enough. Let me know if you are confused with some of the requests.

Is there any way to upload an example?

[karolus]

Hello

Please read my first answer completely, not only the first Paragraph...

Karolus

[sirholm]

You are a genius Thanks alot.

Only part I changed to make it perfect was not to remove duplicates, so it would count how many it was. I works perfect with calculating the products. I made a pivot after this, so it could count how many of each

Thanks again, it's so nice