compare cells, copy, loop



I"m working on an inventory sheet which constantly changes in size.
If there is a way to do this with Functions, i would rather that because
this is a report that is run over night when i'm not here. If not, i guess
we can find a way.

I'd like to look down column A and look at every single item.
If there is no duplicate, do nothing
If there are duplicates, it should first find where A and B = each other for
that particular #. Then take all the cooresponding #'s in B and concatenate
them in Column C (each seperated by a colon) on the row where A and B were =.
see below for a visual.

A1 = 364691-001 B1 = 364691-001 C1 = 364692-001:364695-001
A2 = 364691-001 B2 = 364692-001 C2 = (empty cell)
A3 = 364691-001 B3 = 364695-001 C3 = (empty cell)
A4 = A3509A B4 = A3509A C4 = (empty cell)

A1,A2,A3 are duplicates
A1 and B1 are equal to eachother
Take B2 and B3 and concatenate in C1.
C2 and C3 can stay empty
A4 and B4 = eachother, but there are no duplicates so C4 stays empty

I"m not sure if indexing, Vlookup, if, then, next and loop will be needed or
I'm open to any suggestions.

Bernie Deitrick

Array enter (enter using Ctrl-Shift-Enter) the formula

& IF(COUNTIF($A$1:$A$1000,A1)>=3,":" &INDEX(B:B,SMALL(IF($A$1:$A$1000=A1,ROW($A$1:$A$1000)),3)),"")
& IF(COUNTIF($A$1:$A$1000,A1)>=4,":" &INDEX(B:B,SMALL(IF($A$1:$A$1000=A1,ROW($A$1:$A$1000)),4)),""))

Add additional terms like this inside the final paren, incrementing the X to the count level that
you need (I hope you can see the pattern)

& IF(COUNTIF($A$1:$A$1000,A1)>=X,":" &INDEX(B:B,SMALL(IF($A$1:$A$1000=A1,ROW($A$1:$A$1000)),X)),"")

And change the 1000s to a high enough number to cover all your data.

MS Excel MVP

Bernie Deitrick

I should have added "array enter the formula in C1, then copy down to match your list."

MS Excel MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question