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.

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.

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.