does anyone know how to group post?

I have a database where a customer is listed say 5 times and each time he has
a different order # and service code. The multiple order#'s run vertical
down one column and service codes run vertical down another column and I need
to get each order # and each service code into it's own column.

Ex.

order# service code
123 abc
456 def
789 ghi

Needs to be:

order# order#2 order#3
123 456 789


etc.

Any ideas?

Thanks.

sra wrote...
>I have a database where a customer is listed say 5 times and each time
he has
>a different order # and service code. The multiple order#'s run
vertical
>down one column and service codes run vertical down another column and
I need
>to get each order # and each service code into it's own column.
....

Select the table range, run Data > Filter > AutoFilter, select the
customer name in the customer name field's AutoFilter drop-down list,
copy the filtered results for the order # and service code fields, and
paste special transpose into horizontal ranges.

i'm afraid i didn't give enough info on my first post.

Each customer has an id number in column A, their name in column B, service
code in column C, order # in column D, etc. Each customer may have up to 10
order #'s with different service codes. I need to post the different order
#'s and service codes in their own column based on that customer id field in
column A.

Example:
A=Id
B=name
C=order #
D=service code

A B C
D
123 john sample 11111 abc
123 john sample 22222 def
123 john sample 33333 ghi

Needs to be:

A B C D E
F G H
123 john sample 11111 22222 33333 abc def
ghi

Keep in mind I have over 2,000 records.

Thanks for your help.

sra
XL 03
--



"Harlan Grove" wrote:

> sra wrote...
> >I have a database where a customer is listed say 5 times and each time
> he has
> >a different order # and service code. The multiple order#'s run
> vertical
> >down one column and service codes run vertical down another column and
> I need
> >to get each order # and each service code into it's own column.
> ....
>
> Select the table range, run Data > Filter > AutoFilter, select the
> customer name in the customer name field's AutoFilter drop-down list,
> copy the filtered results for the order # and service code fields, and
> paste special transpose into horizontal ranges.
>
>

"sra" <sradams01@hotmail.com> wrote...
>Each customer has an id number in column A, their name in
>column B, service code in column C, order # in column D,
>etc. Each customer may have up to 10 order #'s with
>different service codes. I need to post the different
>order #'s and service codes in their own column based on
>that customer id field in column A.
....
>_A_ _____B_____ __C__ _D_
>123 john sample 11111 abc
>123 john sample 22222 def
>123 john sample 33333 ghi
>
>Needs to be:
>
>_A_ _____B_____ __C__ __D__ __E__ _F_ _G_ _H_
>123 john sample 11111 22222 33333 abc def ghi
....

This is what 3 transformed records would look like, but if there had been
only the first 2 records for this customer, would service code abc have
appeared in col E? If so, name the table of records Tbl, and try these
formulas in another worksheet.

A2:
=INDEX(Tbl,1,1)

B2:
=INDEX(Tbl,1,2)

C2:
=IF(COLUMN()-COLUMN($C2)<COUNTIF(INDEX(Tbl,0,1),$A2),
INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0),3),
IF(COLUMN()-COLUMN($C2)<2*COUNTIF(INDEX(Tbl,0,1),$A2),
INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0)
-COUNTIF(INDEX(Tbl,0,1),$A2),4),""))

A3:
=INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,1)

B3:
=INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,2)

Fill A3:B3 down as far as needed. Fill C2 right as far as needed, then fill
C2:#2 down as far as needed.

Please forgive my ignorance but not sure what you mean by name the table of
records Tbl.

Thanks
--



"Harlan Grove" wrote:

> "sra" <sradams01@hotmail.com> wrote...
> >Each customer has an id number in column A, their name in
> >column B, service code in column C, order # in column D,
> >etc. Each customer may have up to 10 order #'s with
> >different service codes. I need to post the different
> >order #'s and service codes in their own column based on
> >that customer id field in column A.
> ....
> >_A_ _____B_____ __C__ _D_
> >123 john sample 11111 abc
> >123 john sample 22222 def
> >123 john sample 33333 ghi
> >
> >Needs to be:
> >
> >_A_ _____B_____ __C__ __D__ __E__ _F_ _G_ _H_
> >123 john sample 11111 22222 33333 abc def ghi
> ....
>
> This is what 3 transformed records would look like, but if there had been
> only the first 2 records for this customer, would service code abc have
> appeared in col E? If so, name the table of records Tbl, and try these
> formulas in another worksheet.
>
> A2:
> =INDEX(Tbl,1,1)
>
> B2:
> =INDEX(Tbl,1,2)
>
> C2:
> =IF(COLUMN()-COLUMN($C2)<COUNTIF(INDEX(Tbl,0,1),$A2),
> INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0),3),
> IF(COLUMN()-COLUMN($C2)<2*COUNTIF(INDEX(Tbl,0,1),$A2),
> INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0)
> -COUNTIF(INDEX(Tbl,0,1),$A2),4),""))
>
> A3:
> =INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,1)
>
> B3:
> =INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,2)
>
> Fill A3:B3 down as far as needed. Fill C2 right as far as needed, then fill
> C2:#2 down as far as needed.
>
>
>

I figured out how to name the table of records and the formula in A2 and B2
worked but nothing is happening in the C2 formula.
--



"sra" wrote:

> Please forgive my ignorance but not sure what you mean by name the table of
> records Tbl.
>
> Thanks
> --
>
>
>
> "Harlan Grove" wrote:
>
> > "sra" <sradams01@hotmail.com> wrote...
> > >Each customer has an id number in column A, their name in
> > >column B, service code in column C, order # in column D,
> > >etc. Each customer may have up to 10 order #'s with
> > >different service codes. I need to post the different
> > >order #'s and service codes in their own column based on
> > >that customer id field in column A.
> > ....
> > >_A_ _____B_____ __C__ _D_
> > >123 john sample 11111 abc
> > >123 john sample 22222 def
> > >123 john sample 33333 ghi
> > >
> > >Needs to be:
> > >
> > >_A_ _____B_____ __C__ __D__ __E__ _F_ _G_ _H_
> > >123 john sample 11111 22222 33333 abc def ghi
> > ....
> >
> > This is what 3 transformed records would look like, but if there had been
> > only the first 2 records for this customer, would service code abc have
> > appeared in col E? If so, name the table of records Tbl, and try these
> > formulas in another worksheet.
> >
> > A2:
> > =INDEX(Tbl,1,1)
> >
> > B2:
> > =INDEX(Tbl,1,2)
> >
> > C2:
> > =IF(COLUMN()-COLUMN($C2)<COUNTIF(INDEX(Tbl,0,1),$A2),
> > INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0),3),
> > IF(COLUMN()-COLUMN($C2)<2*COUNTIF(INDEX(Tbl,0,1),$A2),
> > INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0)
> > -COUNTIF(INDEX(Tbl,0,1),$A2),4),""))
> >
> > A3:
> > =INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,1)
> >
> > B3:
> > =INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,2)
> >
> > Fill A3:B3 down as far as needed. Fill C2 right as far as needed, then fill
> > C2:#2 down as far as needed.
> >
> >
> >

This is close but not quite there yet.

Basically it kept only one record but kept only the data from the second
record of three.

The results were:

_A_ ____B_____ ___C___ ___D___ ___E___ ___F___ ___G___ ___H___
123 john sample 22222 def
--



"sra" wrote:

> Please forgive my ignorance but not sure what you mean by name the table of
> records Tbl.
>
> Thanks
> --
>
>
>
> "Harlan Grove" wrote:
>
> > "sra" <sradams01@hotmail.com> wrote...
> > >Each customer has an id number in column A, their name in
> > >column B, service code in column C, order # in column D,
> > >etc. Each customer may have up to 10 order #'s with
> > >different service codes. I need to post the different
> > >order #'s and service codes in their own column based on
> > >that customer id field in column A.
> > ....
> > >_A_ _____B_____ __C__ _D_
> > >123 john sample 11111 abc
> > >123 john sample 22222 def
> > >123 john sample 33333 ghi
> > >
> > >Needs to be:
> > >
> > >_A_ _____B_____ __C__ __D__ __E__ _F_ _G_ _H_
> > >123 john sample 11111 22222 33333 abc def ghi
> > ....
> >
> > This is what 3 transformed records would look like, but if there had been
> > only the first 2 records for this customer, would service code abc have
> > appeared in col E? If so, name the table of records Tbl, and try these
> > formulas in another worksheet.
> >
> > A2:
> > =INDEX(Tbl,1,1)
> >
> > B2:
> > =INDEX(Tbl,1,2)
> >
> > C2:
> > =IF(COLUMN()-COLUMN($C2)<COUNTIF(INDEX(Tbl,0,1),$A2),
> > INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0),3),
> > IF(COLUMN()-COLUMN($C2)<2*COUNTIF(INDEX(Tbl,0,1),$A2),
> > INDEX(Tbl,COLUMN()-COLUMN($C2)+MATCH($A2,INDEX(Tbl,0,1),0)
> > -COUNTIF(INDEX(Tbl,0,1),$A2),4),""))
> >
> > A3:
> > =INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,1)
> >
> > B3:
> > =INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$A$2:$A2))+1,2)
> >
> > Fill A3:B3 down as far as needed. Fill C2 right as far as needed, then fill
> > C2:#2 down as far as needed.
> >
> >
> >