compare cells, copy, loop

[Immortal_Creations]

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.

examples
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
not.
I'm open to any suggestions.

 

[Bernie Deitrick]

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

=IF(A1<>B1,"",IF(COUNTIF($A$1:$A$1000,A1)>=2,INDEX(B:B,SMALL(IF($A$1:$A$1000=A1,ROW($A$1:$A$1000)),2)),"")
& 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.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

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

Sorry,
Bernie
MS Excel MVP