| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help me help a user in our office
Assume the listing of data below:
Num Name Tx Ok Ks 1 Smith 10 15 18 2 Jones 12 14 16 3 Brown 82 500 65 4 White 111 80 60 5 Pinkerton 75 65 55 I need a method to transform as shown below: 1 Smith Tx 10 1 Smith Ok 15 1 Smith Ks 18 and then continue for each subsequent name record above. Basically you are extracting the state column name and the value from that state's column and creating another record. The desire and intent is not to use a macro. I figured a pivot table would accomplish that, but I haven't been able to figure out how yet. Thanks, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me help a user in our office
Here is some code
Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 Rows(i + 1).Resize(2).Insert Cells(i + 1, "A").Value = Cells(i, "A").Value Cells(i + 1, "B").Value = Range("C1").Value Cells(i + 1, "C").Value = Cells(i, "C").Value Cells(i + 2, "A").Value = Cells(i, "A").Value Cells(i + 2, "B").Value = Range("D1").Value Cells(i + 2, "C").Value = Cells(i, "D").Value Cells(i, "C").Value = Cells(i, "B").Value Cells(i, "B").Value = Range("B1").Value Cells(i, "D").ClearContents Next i Rows(1).Delete End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Bruce" wrote in message ... Assume the listing of data below: Num Name Tx Ok Ks 1 Smith 10 15 18 2 Jones 12 14 16 3 Brown 82 500 65 4 White 111 80 60 5 Pinkerton 75 65 55 I need a method to transform as shown below: 1 Smith Tx 10 1 Smith Ok 15 1 Smith Ks 18 and then continue for each subsequent name record above. Basically you are extracting the state column name and the value from that state's column and creating another record. The desire and intent is not to use a macro. I figured a pivot table would accomplish that, but I haven't been able to figure out how yet. Thanks, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me help a user in our office
Here is some code
Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 Rows(i + 1).Resize(2).Insert Cells(i + 1, "A").Value = Cells(i, "A").Value Cells(i + 1, "B").Value = Range("C1").Value Cells(i + 1, "C").Value = Cells(i, "C").Value Cells(i + 2, "A").Value = Cells(i, "A").Value Cells(i + 2, "B").Value = Range("D1").Value Cells(i + 2, "C").Value = Cells(i, "D").Value Cells(i, "C").Value = Cells(i, "B").Value Cells(i, "B").Value = Range("B1").Value Cells(i, "D").ClearContents Next i Rows(1).Delete End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Bruce" wrote in message ... Assume the listing of data below: Num Name Tx Ok Ks 1 Smith 10 15 18 2 Jones 12 14 16 3 Brown 82 500 65 4 White 111 80 60 5 Pinkerton 75 65 55 I need a method to transform as shown below: 1 Smith Tx 10 1 Smith Ok 15 1 Smith Ks 18 and then continue for each subsequent name record above. Basically you are extracting the state column name and the value from that state's column and creating another record. The desire and intent is not to use a macro. I figured a pivot table would accomplish that, but I haven't been able to figure out how yet. Thanks, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me help a user in our office
Ok Bob, I'll fiddle with the code to see how it works, but my user will be
skiddish about macro use. Do you not think what I describe can be done without code? Bruce "Bob Phillips" wrote: Here is some code Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 Rows(i + 1).Resize(2).Insert Cells(i + 1, "A").Value = Cells(i, "A").Value Cells(i + 1, "B").Value = Range("C1").Value Cells(i + 1, "C").Value = Cells(i, "C").Value Cells(i + 2, "A").Value = Cells(i, "A").Value Cells(i + 2, "B").Value = Range("D1").Value Cells(i + 2, "C").Value = Cells(i, "D").Value Cells(i, "C").Value = Cells(i, "B").Value Cells(i, "B").Value = Range("B1").Value Cells(i, "D").ClearContents Next i Rows(1).Delete End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Bruce" wrote in message ... Assume the listing of data below: Num Name Tx Ok Ks 1 Smith 10 15 18 2 Jones 12 14 16 3 Brown 82 500 65 4 White 111 80 60 5 Pinkerton 75 65 55 I need a method to transform as shown below: 1 Smith Tx 10 1 Smith Ok 15 1 Smith Ks 18 and then continue for each subsequent name record above. Basically you are extracting the state column name and the value from that state's column and creating another record. The desire and intent is not to use a macro. I figured a pivot table would accomplish that, but I haven't been able to figure out how yet. Thanks, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me help a user in our office
Bruce, here is an alternative using formulas.
Assumptions: - The original table starts from A1 (headers). - The columns I will present start from G2. (G1:K1) hold headers. Column G:G: Aux. Start with the numbers 0, 1, 2, ... as far down as necessary (i.e. 3*number of data rows - 1) Column H:H: Num: In H2: =OFFSET($A$2,INT(G2/3),0) Column I:I: Name: In I2: =VLOOKUP(H2,A:E,2,0) Column J:J: State: In J2: =OFFSET($C$1,0,MOD(G2,3)) Column K:K: Value: In K2: =VLOOKUP(H2,A:E,MATCH(J2,$A$1:$E$1,0),0) HTH Kostis Vezerides |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me help a user in our office
Bruce,
Here is an alternative formula solution Assuming this data is on Sheet1, then on Sheet2 cell A1: =INDIRECT("Sheet3!A"&(INT((ROW()-1)/3)+2)) cell B1: =INDEX(Sheet3!$B$1:$D$1,,MOD(ROW()-1,3)+1) cell C1: =INDEX(INDIRECT("Sheet3!$B"&INT((ROW()-1)/3)+2&":$D"&INT((ROW()-1)/3)+2),,MO D(ROW()-1,3)+1) anjd copy down until it goes bad -- HTH RP (remove nothere from the email address if mailing direct) "Bruce" wrote in message ... Ok Bob, I'll fiddle with the code to see how it works, but my user will be skiddish about macro use. Do you not think what I describe can be done without code? Bruce "Bob Phillips" wrote: Here is some code Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 Rows(i + 1).Resize(2).Insert Cells(i + 1, "A").Value = Cells(i, "A").Value Cells(i + 1, "B").Value = Range("C1").Value Cells(i + 1, "C").Value = Cells(i, "C").Value Cells(i + 2, "A").Value = Cells(i, "A").Value Cells(i + 2, "B").Value = Range("D1").Value Cells(i + 2, "C").Value = Cells(i, "D").Value Cells(i, "C").Value = Cells(i, "B").Value Cells(i, "B").Value = Range("B1").Value Cells(i, "D").ClearContents Next i Rows(1).Delete End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Bruce" wrote in message ... Assume the listing of data below: Num Name Tx Ok Ks 1 Smith 10 15 18 2 Jones 12 14 16 3 Brown 82 500 65 4 White 111 80 60 5 Pinkerton 75 65 55 I need a method to transform as shown below: 1 Smith Tx 10 1 Smith Ok 15 1 Smith Ks 18 and then continue for each subsequent name record above. Basically you are extracting the state column name and the value from that state's column and creating another record. The desire and intent is not to use a macro. I figured a pivot table would accomplish that, but I haven't been able to figure out how yet. Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pretty simple question | Excel Discussion (Misc queries) | |||
| Inheriting a laptop--how do I change office user account informati | Excel Discussion (Misc queries) | |||
| TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
| Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) | |||
| how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) | |||
Linear Mode
