does anyone know how to group post?

[sra]

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.

 

[Harlan Grove]

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]

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]

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.

 

[sra]

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

Thanks

 

[sra]

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]

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

 

[Harlan Grove]

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

....

I generally try to stick to prose, but a binary file may make it clearer
what you need to do. I've put an example at

ftp://members.aol.com/hrlngrv/mpewf.20050505.xls

The key formula is the one in the 3rd column of the top row of the result
range. In my previous reply, that was cell C2. Here's the formula again with
the cell changed to E17. The cell addresses surrounded by spaces need to be
adjusted relative to the cell in which you enter this top-left formula.

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

If you enter this top-left formula in some cell #, then you need to replace
the $C17 references in the formula above with references to the cell 2
columns to the left of # with the column absolute and the $E17 references
with references to # with the column absolute.

 

[sra]

I am sooooo close.

As you can see in your example, there are order #'s in the same column as
service codes. Is there a way to keep that from happening or a way to sort
them out after the fact?

Thanks for all of your help!

sra

 

[Harlan Grove]

sra wrote...

As you can see in your example, there are order #'s in the same column as
service codes. Is there a way to keep that from happening or a way to sort
them out after the fact?

....

That's why I asked a few responses ago,

"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 you want all order numbers to appear to the left of all service
codes, you need to accept some unused columns between the last order
number and the first service code for accounts with fewer than the
maximum number of orders. And you'd need to hard-code that maximum
number of orders.

The main alternative would be using pairs of columns going left to
right to hold order numbers in the first column of the paid and
corresponding service code in the second column.

Which do you want?

 

[sra]

The answer to that question is yes.

I would like to see all order numbers appear to the left of all service codes.

One other thing I'm seeing is when there is say the first three records
match and column C is blank, it puts a 0 in columns C,D & E (3 columns for 3
records I'm assuming) and then posts the data from column D in F,G & H (3
times) in the new worksheet.

Thanks again for your help.

sra

 

[Harlan Grove]

sra wrote...

I would like to see all order numbers appear to the left of all

service codes.

Then you need to figure out the greatest number of records you'd have
for a single customer. If it were 6, then try these formulas with the
top-left cell E2.

E2:
=INDEX(Tbl,1,1)

F2:
=INDEX(Tbl,1,2)

E3:
=INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$E$2:$E2))+1,1)

F3:
=INDEX(Tbl,SUMPRODUCT(COUNTIF(INDEX(Tbl,0,1),$E$2:$E2))+1,2)

Note that E3 and F3 refer to $E$2:$E2. If you enter top-left cell is
somewhere else, modify this term to refer to your top-left cell. Fill
E3:F3 down as far as needed.

G2:
=IF(COLUMN()-COLUMN($G2)<COUNTIF(INDEX(Tbl,0,1),$E2),
INDEX(Tbl,COLUMN()-COLUMN($G2)+MATCH($E2,INDEX(Tbl,0,1),0),3),"")

Note that G2 contains references to $E2 (the top-left cell) and $G2
(itself). If you enter this in some other cell, adjust these references
accordingly. Fill G2 right into I2:L2.

M2:
=IF(COLUMN()-COLUMN($M2)<COUNTIF(INDEX(Tbl,0,1),$E2),
INDEX(Tbl,COLUMN()-COLUMN($M2)+MATCH($E2,INDEX(Tbl,0,1),0),4),"")

Note that M2 contains references to $E2 (the top-left cell) and $M2
(itself). If you enter this in some other cell, adjust these references
accordingly. Fill M2 right into N2:R2. Then fill G2:R2 down into the
same rows filled with formulas in columns E and F.

One other thing I'm seeing is when there is say the first three records
match and column C is blank, it puts a 0 in columns C,D & E (3 columns for 3
records I'm assuming) and then posts the data from column D in F,G & H (3
times) in the new worksheet.

So there'd be account entries in the first two columns with no order
numbers or service codes in the second two columns? The expedient thing
to do would be to make the blank cells in the 3rd and 4th columns of
Tbl zero length strings. Easiest to select the 3rd and 4th columns in
TBL, Edit > Goto, click on Special, select the Blanks radio button,
click OK. This should have selected the blank cells in the 3rd and 4th
columns of Tbl. Type a single apostrophe and press [Ctrl]+[Enter]. This
should enter a label prefix character in each of these cells,
converting them from blank cells to cells evaluating to zero length
strings. If you want to do this in the formulas, wrap the INDEX calls
inside N() if they should always be numbers or T() if they should
always be text strings. So if order numbers are numbers and service
codes are text,

G2:
=IF(COLUMN()-COLUMN($G2)<COUNTIF(INDEX(Tbl,0,1),$E2),
N(INDEX(Tbl,COLUMN()-COLUMN($G2)+MATCH($E2,INDEX(Tbl,0,1),0),3)),"")

M2:
=IF(COLUMN()-COLUMN($M2)<COUNTIF(INDEX(Tbl,0,1),$E2),
T(INDEX(Tbl,COLUMN()-COLUMN($M2)+MATCH($E2,INDEX(Tbl,0,1),0),4)),"")