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(N30,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.

KNS,

In cell O3, use the formula

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

In cell P3, use the formula

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

Select O3:P3, 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" wrote in message
...
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(N30,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.

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(J 2,N2)),IF(A2=A6,CONCATENATE(REPT(J6,N6),REPT(J5,N5 ),REPT(J4,N4),REPT(J3,N3),REPT(J2,N2)),IF(A2=A5,CO NCATENATE(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

"Bernie Deitrick" wrote:

KNS,

In cell O3, use the formula

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

In cell P3, use the formula

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

Select O3:P3, 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" wrote in message
...
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(N30,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.