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

Sorry,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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$10 00)),3)),"") &
IF(COUNTIF($A$1:$A$1000,A1)=4,":" &INDEX(B:B,SMALL(IF($A$1:$A$1000=A1,ROW($A$1:$A$10 00)),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$10 00)),X)),"")

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

HTH,
Bernie
MS Excel MVP


"Immortal_Creations" wrote in message
...
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.