Combining Text from multiple cells under multiple conditions

[KNS]

I've already gotten a partial answer to my question, but my boss is pushing
me to try and find a solution faster, Basically, I have a spreadsheet full of
orders, with a separate line in excel for every item regardless of whether
the items are on the same order or not. I need to combine the items so that
they print in a single cell each item in the order, and that item the number
of times it appears, so something along the lines of
=IF(N3>0,REPT(J3,N3),IF(A3=A4,J4,"")) Where N3 is the quantity of a single
item, J3 is the item SKU, and column A is the order numbers, which when the
same means a multi-SKU order. Is this possible? If it is, is there a way to
have it compare orders numbers until it comes to one that is different,
because theoretically we could have 20 SKUs on a single order. IF this isn't
clear please ask for clarification of the point that is confusin.

 

[Bernie Deitrick]

KNS,

In cell O3, use the formula

=IF(A2=A3,IF(O2<>"",O2&", ","") &IF(N3>1,REPT(J3&",
",N3-1)&J3,J3),IF(N3>1,REPT(J3&", ",N3-1)&J3,IF(N3<>"",J3,"")))

In cell P3, use the formula

=IF(A3=A4,"",O3)

Select O33, and copy down to match your data table. The non-blank cells in
column O will contain the strings for each order.

If that doesn't do what you want, post a small table of your data, and what
result you would like.

HTH,
Bernie
MS Excel MVP

 

[KNS]

I couldn't ever get that to work, I did manage to make this formula do what
I need, but we do have occasional orders which exceed the amount this formula
is able to handle. The other thing that would be great, is if there's a way
to shorten the formula so that I can put spaces between the items, because as
of now, it exceeds the formula size that excel will allow me.

=IF(A1=A2,X2,IF(A2=A7,CONCATENATE(REPT(J7,N7),REPT(J6,N6),REPT(J5,N5),REPT(J4,N4),REPT(J3,N3),REPT(J2,N2)),IF(A2=A6,CONCATENATE(REPT(J6,N6),REPT(J5,N5),REPT(J4,N4),REPT(J3,N3),REPT(J2,N2)),IF(A2=A5,CONCATENATE(REPT(J5,N5),REPT(J4,N4),REPT(J3,N3),REPT(J2,N2)),IF(A2=A4,CONCATENATE(REPT(J4,N4),REPT(J3,N3),REPT(J2,N2)),IF(A2=A3,CONCATENATE(REPT(J2,N2),REPT(J3,N3)),REPT(J2,N2)))))))

I can't get a table to copy into this window, but our data fits as follows
A=Order #, B=First Name, C=Last Name, D=Address, E=Address 2, F=Address City,
G=Address State, H=Address Zip, I=Ship to Method, J=Product ID, K=Sold For,
L=Our Cost, M=Quantity, n=Description, O=Carrier Requested, P=Shipping Cost

Thanks for your help.
Zach
KNS