Ok, rather than data flipping, maybe this can be done with a sophisticated
formula. IF I had the customer numbers lined up (ie all 145's were in
column B, all 167's were in column C), I could write a VLookup to find
CLAIMED PHONE, and then the column index number I would use a Find statement
to find the customer #. But since the customers are in different columns,
how could I use the find? Conceptually, I would - Do the VLookup for
CLAIMED PHONE, and use the Find to locate the customer #, but the find would
look in the range exactly 1 row above where the Vlookup found CLAIMED PHONE.
I'm pretty sure that has to be a VB formula. Am I on the right track? Is
something like this possible?

Customer 145 167 179 182 183
CLAIMED PHONE 1 32172 1 27912 542

Customer 167 182 Total
NO STATUS 1 1 2

Customer 167 182 183 189 205
NON DELIVERABLE 2167 1109 7 243 2

Hi,
Some basic "starter code" which assumes the Text is column A
("CUSTOMER" & "CLAIMED PHONE "and data in columm B onwards. This places
output in column J (for demo only).

You need to add looping logic (which change values of r and rr) to transpose
all your data.

Try with this data in rows 1! and 2

Customer 145 167 179 182 183
CLAIMED PHONE 1 32172 1 27912 542


r = 1 ' First row of input
rr = 2 ' first row of output
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(r, 2), Cells(r + 1, Lastcol)).Copy
Cells(rr, 10) = Cells(r + 1, "A") ' Copy title i.e row 2 data
Cells(rr + 1, 10).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True


HTH


"Steph" wrote:

Ok, rather than data flipping, maybe this can be done with a sophisticated
formula. IF I had the customer numbers lined up (ie all 145's were in
column B, all 167's were in column C), I could write a VLookup to find
CLAIMED PHONE, and then the column index number I would use a Find statement
to find the customer #. But since the customers are in different columns,
how could I use the find? Conceptually, I would - Do the VLookup for
CLAIMED PHONE, and use the Find to locate the customer #, but the find would
look in the range exactly 1 row above where the Vlookup found CLAIMED PHONE.
I'm pretty sure that has to be a VB formula. Am I on the right track? Is
something like this possible?

Customer 145 167 179 182 183
CLAIMED PHONE 1 32172 1 27912 542

Customer 167 182 Total
NO STATUS 1 1 2

Customer 167 182 183 189 205
NON DELIVERABLE 2167 1109 7 243 2

Some more useful code. Assumes input data is in consecutive pairs of rows i.e
1,2/3,4 etc. Output is to a new sheet.

Sub FlipData()

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

rr = 1 ' first row of output
ws1.Activate
Lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row


With ws1
For r = 1 To Lastrow Step 2
Lastcol = .Cells(r, Columns.Count).End(xlToLeft).Column
.Range(.Cells(r, 2), .Cells(r + 1, Lastcol)).Copy
ws2.Cells(rr, 1) = .Cells(r + 1, "A") ' Copy title i.e row 2 data
ws2.Cells(rr + 1, 1).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
rr = rr + Lastcol + 1
Next r
End With
End Sub


HTH

"Toppers" wrote:

Hi,
Some basic "starter code" which assumes the Text is column A
("CUSTOMER" & "CLAIMED PHONE "and data in columm B onwards. This places
output in column J (for demo only).

You need to add looping logic (which change values of r and rr) to transpose
all your data.

Try with this data in rows 1! and 2

Customer 145 167 179 182 183
CLAIMED PHONE 1 32172 1 27912 542


r = 1 ' First row of input
rr = 2 ' first row of output
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(r, 2), Cells(r + 1, Lastcol)).Copy
Cells(rr, 10) = Cells(r + 1, "A") ' Copy title i.e row 2 data
Cells(rr + 1, 10).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True


HTH


"Steph" wrote:

Ok, rather than data flipping, maybe this can be done with a sophisticated
formula. IF I had the customer numbers lined up (ie all 145's were in
column B, all 167's were in column C), I could write a VLookup to find
CLAIMED PHONE, and then the column index number I would use a Find statement
to find the customer #. But since the customers are in different columns,
how could I use the find? Conceptually, I would - Do the VLookup for
CLAIMED PHONE, and use the Find to locate the customer #, but the find would
look in the range exactly 1 row above where the Vlookup found CLAIMED PHONE.
I'm pretty sure that has to be a VB formula. Am I on the right track? Is
something like this possible?

Customer 145 167 179 182 183
CLAIMED PHONE 1 32172 1 27912 542

Customer 167 182 Total
NO STATUS 1 1 2

Customer 167 182 183 189 205
NON DELIVERABLE 2167 1109 7 243 2

Fantastic! Thanks so much for your help!!!!!!

"Toppers" wrote in message
...
Some more useful code. Assumes input data is in consecutive pairs of rows
i.e
1,2/3,4 etc. Output is to a new sheet.

Sub FlipData()

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

rr = 1 ' first row of output
ws1.Activate
Lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row


With ws1
For r = 1 To Lastrow Step 2
Lastcol = .Cells(r, Columns.Count).End(xlToLeft).Column
.Range(.Cells(r, 2), .Cells(r + 1, Lastcol)).Copy
ws2.Cells(rr, 1) = .Cells(r + 1, "A") ' Copy title i.e row 2
data
ws2.Cells(rr + 1, 1).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
rr = rr + Lastcol + 1
Next r
End With
End Sub


HTH

"Toppers" wrote:

Hi,
Some basic "starter code" which assumes the Text is column A
("CUSTOMER" & "CLAIMED PHONE "and data in columm B onwards. This places
output in column J (for demo only).

You need to add looping logic (which change values of r and rr) to
transpose
all your data.

Try with this data in rows 1! and 2

Customer 145 167 179 182 183
CLAIMED PHONE 1 32172 1 27912 542


r = 1 ' First row of input
rr = 2 ' first row of output
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(r, 2), Cells(r + 1, Lastcol)).Copy
Cells(rr, 10) = Cells(r + 1, "A") ' Copy title i.e row 2 data
Cells(rr + 1, 10).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True


HTH


"Steph" wrote:

Ok, rather than data flipping, maybe this can be done with a
sophisticated
formula. IF I had the customer numbers lined up (ie all 145's were in
column B, all 167's were in column C), I could write a VLookup to find
CLAIMED PHONE, and then the column index number I would use a Find
statement
to find the customer #. But since the customers are in different
columns,
how could I use the find? Conceptually, I would - Do the VLookup for
CLAIMED PHONE, and use the Find to locate the customer #, but the find
would
look in the range exactly 1 row above where the Vlookup found CLAIMED
PHONE.
I'm pretty sure that has to be a VB formula. Am I on the right track?
Is
something like this possible?

Customer 145 167 179 182 183
CLAIMED PHONE 1 32172 1 27912 542

Customer 167 182 Total
NO STATUS 1 1 2

Customer 167 182 183 189 205
NON DELIVERABLE 2167 1109 7 243 2