does anyone know how to group post?

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?

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" wrote:

> 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 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)),"")