Transposing one column into three
I have 2 columns of about 100 records.
First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance |
Transposing one column into three
Gilbert,
If you want to do this with formulae: Assuming: Column A contains the vendor name starting at row 2, B contains ""I, II, or III" starting at row 2 Create: Column C with header "I" in row 1 Column D with header "II" in row 1 Column E with header "III" in row 1 In cell C2 type the formula: =IF($B2=C$1,$A2,"") and copy the formula to all cells in columns C to E which have row data. That will, I think, give you a tabular layout. AlexJ "Gilbert" wrote in message ... I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance |
Transposing one column into three
I tried that as I pointed out in my post, but I get blank
rows in each column. I 'd like to have three consistent columns with no blanks between the rows... Thanks anyway. -----Original Message----- Gilbert, If you want to do this with formulae: Assuming: Column A contains the vendor name starting at row 2, B contains ""I, II, or III" starting at row 2 Create: Column C with header "I" in row 1 Column D with header "II" in row 1 Column E with header "III" in row 1 In cell C2 type the formula: =IF($B2=C$1,$A2,"") and copy the formula to all cells in columns C to E which have row data. That will, I think, give you a tabular layout. AlexJ "Gilbert" wrote in message ... I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance . |
Transposing one column into three
Sorry if I tried to tell you something you know already.
I don't understand what you mean when you say "no blanks between the rows" Are there gaps in the records, or do you wish to have the output sorted? AlexJ "Gilbert" wrote in message ... I tried that as I pointed out in my post, but I get blank rows in each column. I 'd like to have three consistent columns with no blanks between the rows... Thanks anyway. -----Original Message----- Gilbert, If you want to do this with formulae: Assuming: Column A contains the vendor name starting at row 2, B contains ""I, II, or III" starting at row 2 Create: Column C with header "I" in row 1 Column D with header "II" in row 1 Column E with header "III" in row 1 In cell C2 type the formula: =IF($B2=C$1,$A2,"") and copy the formula to all cells in columns C to E which have row data. That will, I think, give you a tabular layout. AlexJ "Gilbert" wrote in message ... I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance . |
Transposing one column into three
Alex,
No, the source column has no gaps in it but since the I's II's and III's are allocated to a vendor in a non- predictable way, when I try to scan all the column for instances of I's when the formula finds one, it placesit in the same relative row in the target column (under the I header) same for II and III therefore I get an accurate result but not usable for presentation. If I sort my source column (I.II,III), before running the formula I get a better result for I only get one block of blank rows in each target column I guess I just need to sort each target column nder I, II,III. I didn't mean to offend you or anyone by saying that I had already tried that solution. Thanks anyway for any past and future help. G -----Original Message----- Sorry if I tried to tell you something you know already. I don't understand what you mean when you say "no blanks between the rows" Are there gaps in the records, or do you wish to have the output sorted? AlexJ "Gilbert" wrote in message ... I tried that as I pointed out in my post, but I get blank rows in each column. I 'd like to have three consistent columns with no blanks between the rows... Thanks anyway. -----Original Message----- Gilbert, If you want to do this with formulae: Assuming: Column A contains the vendor name starting at row 2, B contains ""I, II, or III" starting at row 2 Create: Column C with header "I" in row 1 Column D with header "II" in row 1 Column E with header "III" in row 1 In cell C2 type the formula: =IF($B2=C$1,$A2,"") and copy the formula to all cells in columns C to E which have row data. That will, I think, give you a tabular layout. AlexJ "Gilbert" wrote in message ... I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance . . |
Transposing one column into three
Sub ChangeData()
'Assumptions 'Column headings in row 1 'Data starts in row 2 'VendorName is in column A 'Type (I,II,III) is in column B Dim ws As Worksheet, wsNew As Worksheet Dim c As Range Dim lngVendorI As Long Dim lngVendorII As Long Dim lngVendorIII As Long Set ws = ActiveSheet ActiveWorkbook.Worksheets.Add Set wsNew = ActiveSheet With wsNew .Range("A1").Value = "I" .Range("B1").Value = "II" .Range("C1").Value = "III" End With lngVendorI = 2 lngVendorII = 2 lngVendorIII = 2 For Each c In ws.Range("B2:B" & ws.Range("B65536").End(xlUp).Row) Select Case c.Value Case "I" wsNew.Cells(lngVendorI, 1).Value = c.Offset(0, -1).Value lngVendorI = lngVendorI + 1 Case "II" wsNew.Cells(lngVendorII, 2).Value = c.Offset(0, -1).Value lngVendorII = lngVendorII + 1 Case "III" wsNew.Cells(lngVendorIII, 3).Value = c.Offset(0, -1).Value lngVendorIII = lngVendorIII + 1 End Select Next c Set c = Nothing Set ws = Nothing Set wsNew = Nothing End Sub -- Dianne In , Gilbert typed: I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance |
Transposing one column into three
No offense taken. Sorry I coldn't help
Alex "Gilbert" wrote in message ... Alex, No, the source column has no gaps in it but since the I's II's and III's are allocated to a vendor in a non- predictable way, when I try to scan all the column for instances of I's when the formula finds one, it placesit in the same relative row in the target column (under the I header) same for II and III therefore I get an accurate result but not usable for presentation. If I sort my source column (I.II,III), before running the formula I get a better result for I only get one block of blank rows in each target column I guess I just need to sort each target column nder I, II,III. I didn't mean to offend you or anyone by saying that I had already tried that solution. Thanks anyway for any past and future help. G -----Original Message----- Sorry if I tried to tell you something you know already. I don't understand what you mean when you say "no blanks between the rows" Are there gaps in the records, or do you wish to have the output sorted? AlexJ "Gilbert" wrote in message ... I tried that as I pointed out in my post, but I get blank rows in each column. I 'd like to have three consistent columns with no blanks between the rows... Thanks anyway. -----Original Message----- Gilbert, If you want to do this with formulae: Assuming: Column A contains the vendor name starting at row 2, B contains ""I, II, or III" starting at row 2 Create: Column C with header "I" in row 1 Column D with header "II" in row 1 Column E with header "III" in row 1 In cell C2 type the formula: =IF($B2=C$1,$A2,"") and copy the formula to all cells in columns C to E which have row data. That will, I think, give you a tabular layout. AlexJ "Gilbert" wrote in message ... I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance . . |
Transposing one column into three
This looks great!!
I'm going to try it right away I'll keep you posted.. Thanks a million.... G -----Original Message----- Sub ChangeData() 'Assumptions 'Column headings in row 1 'Data starts in row 2 'VendorName is in column A 'Type (I,II,III) is in column B Dim ws As Worksheet, wsNew As Worksheet Dim c As Range Dim lngVendorI As Long Dim lngVendorII As Long Dim lngVendorIII As Long Set ws = ActiveSheet ActiveWorkbook.Worksheets.Add Set wsNew = ActiveSheet With wsNew .Range("A1").Value = "I" .Range("B1").Value = "II" .Range("C1").Value = "III" End With lngVendorI = 2 lngVendorII = 2 lngVendorIII = 2 For Each c In ws.Range("B2:B" & ws.Range("B65536").End (xlUp).Row) Select Case c.Value Case "I" wsNew.Cells(lngVendorI, 1).Value = c.Offset(0, -1).Value lngVendorI = lngVendorI + 1 Case "II" wsNew.Cells(lngVendorII, 2).Value = c.Offset(0, -1).Value lngVendorII = lngVendorII + 1 Case "III" wsNew.Cells(lngVendorIII, 3).Value = c.Offset(0, -1).Value lngVendorIII = lngVendorIII + 1 End Select Next c Set c = Nothing Set ws = Nothing Set wsNew = Nothing End Sub -- Dianne In , Gilbert typed: I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance . |
Transposing one column into three
Gilbert,
The code I posted will create a new worksheet to hold the data. If you want to name that sheet then just change With wsNew .Range("A1").Value = "I" .Range("B1").Value = "II" .Range("C1").Value = "III" End With to With wsNew .Name = "Priorities" .Range("A1").Value = "I" .Range("B1").Value = "II" .Range("C1").Value = "III" End With If this is not what you want, let me know. -- Dianne In , Gilbert typed: It worked perfectly, and so quickly!! Dianne, thanks a lot Would it change a lot tohe code if I had to keep the source data in its own worksheet? In other words The new data you've changed needs to be displayed in its own spreadsheet called "Priorities" Thnks anyway for your extremey knowledgable help. -----Original Message----- Sub ChangeData() 'Assumptions 'Column headings in row 1 'Data starts in row 2 'VendorName is in column A 'Type (I,II,III) is in column B Dim ws As Worksheet, wsNew As Worksheet Dim c As Range Dim lngVendorI As Long Dim lngVendorII As Long Dim lngVendorIII As Long Set ws = ActiveSheet ActiveWorkbook.Worksheets.Add Set wsNew = ActiveSheet With wsNew .Range("A1").Value = "I" .Range("B1").Value = "II" .Range("C1").Value = "III" End With lngVendorI = 2 lngVendorII = 2 lngVendorIII = 2 For Each c In ws.Range("B2:B" & ws.Range("B65536").End (xlUp).Row) Select Case c.Value Case "I" wsNew.Cells(lngVendorI, 1).Value = c.Offset(0, -1).Value lngVendorI = lngVendorI + 1 Case "II" wsNew.Cells(lngVendorII, 2).Value = c.Offset(0, -1).Value lngVendorII = lngVendorII + 1 Case "III" wsNew.Cells(lngVendorIII, 3).Value = c.Offset(0, -1).Value lngVendorIII = lngVendorIII + 1 End Select Next c Set c = Nothing Set ws = Nothing Set wsNew = Nothing End Sub -- Dianne In , Gilbert typed: I have 2 columns of about 100 records. First record is a vendor name, second record is either I,II, or III. All vendor names are unique. Out of this data I would like to build three columns. One for "I" one for "II" and the last for "III". Of course under these header would be the vendors names. I was able to do it but I have three columns with blank rows scattered all over. I would appreciate any help or hint. Thanks in advance . |
Transposing one column into three
Dianne,
When I run your macro I get the result in a spreadsheet named "sheet1" it basically creates a worksheet. I 've checked three times and it looks like teh macro replaces my existing worksheet with the new one. I still need my source data to retriene other elements of information. Thanks |
Transposing one column into three
|
Transposing one column into three
OK, I think I understand now... You want to collect that data in an
existing worksheet rather than creating a brand new one? If so, assuming the existing worksheet is called "Priorities", change: Set ws = ActiveSheet ActiveWorkbook.Worksheets.Add Set wsNew = ActiveSheet to Set ws = ActiveSheet Set wsNew = ActiveWorkbook.Worksheets("Priorities") If your worksheet is not called Priorities, then just change Worksheets("Priorities") to Worksheets("WhateverYourSheetNameIs"). Also, if you didn't want the restriction of running the macro from the sheet where your data is held, you can change Set ws = ActiveSheet to Set ws = ActiveWorkbook.Worksheets("NameOfSheetWithData") Let me know if I've misunderstood what you want. -- HTH, Dianne In , Gilbert typed: Dianne, When I run your macro I get the result in a spreadsheet named "sheet1" it basically creates a worksheet. I 've checked three times and it looks like teh macro replaces my existing worksheet with the new one. I still need my source data to retriene other elements of information. Thanks |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com