Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
create 1 master list from a combination of rows and columns
Hi there
I have a spreadsheet that contains approx 584 rows and 163 columns. Column A = Account, Column B = Description, Columns C+ = Department. Something like this ACCT DESCRIPTION 1101 2140 3125 6179 12345 Example 1 X 22588 Example 1 X X X 33244 Example 1 X X 78544 Example 1 X X X 78545 Example 1 X X Is it possible to use VBA to achieve the following: ACCT DESCRIPTION DEPT 12345 Example 1 1101 22588 Example 1 1101 22588 Example 1 2140 22588 Example 1 3125 33244 Example 1 2140 33244 Example 1 3125 78544 Example 1 2140 78544 Example 1 3125 78544 Example 1 6179 78545 Example 1 3125 78545 Example 1 6179 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
create 1 master list from a combination of rows and columns
Something like this:
Sub TransposeColumns() 'Start row of destination i = 2 'Range of Departments For Each cell In Range("C2:F10") If cell.Value = "X" Then xRow = cell.Row xColumn = cell.Column 'Change first part of formula to destination 'columns as desired Cells(i, "G").Value = Cells(xRow, "A") Cells(i, "H").Value = Cells(xRow, "B") Cells(i, "I").Value = Cells(1, xColumn) i = i + 1 End If Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jason" wrote: Hi there I have a spreadsheet that contains approx 584 rows and 163 columns. Column A = Account, Column B = Description, Columns C+ = Department. Something like this ACCT DESCRIPTION 1101 2140 3125 6179 12345 Example 1 X 22588 Example 1 X X X 33244 Example 1 X X 78544 Example 1 X X X 78545 Example 1 X X Is it possible to use VBA to achieve the following: ACCT DESCRIPTION DEPT 12345 Example 1 1101 22588 Example 1 1101 22588 Example 1 2140 22588 Example 1 3125 33244 Example 1 2140 33244 Example 1 3125 78544 Example 1 2140 78544 Example 1 3125 78544 Example 1 6179 78545 Example 1 3125 78545 Example 1 6179 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
create 1 master list from a combination of rows and columns
Jason
Try this. I assumed that you had a sheet named Utility and that the resulting table will be placed in that sheet. HTH Otto Sub ReArrange() Dim rColA As Range, i As Range, TheRng As Range Dim j As Range, Dest As Range Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) With Sheets("Utility") Set Dest = .Range("A2") For Each i In rColA If Cells(i.Row, Columns.Count).End(xlToLeft).Column 2 Then Set TheRng = Range(Cells(i.Row, 3), Cells(i.Row, Columns.Count).End(xlToLeft)) For Each j In TheRng If Not IsEmpty(j.Value) Then Dest = i.Value Dest.Offset(, 1) = i.Offset(, 1).Value Dest.Offset(, 2) = Cells(1, j.Column).Value Set Dest = Dest.Offset(1) End If Next j End If Next i End With End Sub "Jason" wrote in message ... Hi there I have a spreadsheet that contains approx 584 rows and 163 columns. Column A = Account, Column B = Description, Columns C+ = Department. Something like this ACCT DESCRIPTION 1101 2140 3125 6179 12345 Example 1 X 22588 Example 1 X X X 33244 Example 1 X X 78544 Example 1 X X X 78545 Example 1 X X Is it possible to use VBA to achieve the following: ACCT DESCRIPTION DEPT 12345 Example 1 1101 22588 Example 1 1101 22588 Example 1 2140 22588 Example 1 3125 33244 Example 1 2140 33244 Example 1 3125 78544 Example 1 2140 78544 Example 1 3125 78544 Example 1 6179 78545 Example 1 3125 78545 Example 1 6179 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting data from a combination of columns & rows. | Excel Discussion (Misc queries) | |||
Create A Master List From Several Lists | Excel Worksheet Functions | |||
How can I create a master item list from orders? | Excel Worksheet Functions | |||
how to create a list and link it to the master excel file | Excel Worksheet Functions | |||
create a master list from other sheets | Excel Worksheet Functions |