Combining Text from multiple cells under multiple conditions

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 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" <KNS@discussions.microsoft.com> wrote in message
news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> 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.
>
>
>

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

"Bernie Deitrick" wrote:

> 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 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" <KNS@discussions.microsoft.com> wrote in message
> news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> > 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.
> >
> >
> >
>
>
>

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 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" <KNS@discussions.microsoft.com> wrote in message
news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> 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.
>
>
>

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

"Bernie Deitrick" wrote:

> 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 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" <KNS@discussions.microsoft.com> wrote in message
> news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> > 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.
> >
> >
> >
>
>
>

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 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" <KNS@discussions.microsoft.com> wrote in message
news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> 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.
>
>
>

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

"Bernie Deitrick" wrote:

> 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 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" <KNS@discussions.microsoft.com> wrote in message
> news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> > 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.
> >
> >
> >
>
>
>

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.

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 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" <KNS@discussions.microsoft.com> wrote in message
news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> 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.
>
>
>

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

"Bernie Deitrick" wrote:

> 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 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" <KNS@discussions.microsoft.com> wrote in message
> news:67EB82BA-ADA2-40B7-B065-500115684D60@microsoft.com...
> > 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.
> >
> >
> >
>
>
>