Data Flipping - Approach with formula?

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> > >
> > >
> > >