|
multiple ranges, transposing?
I start with a list like this
company carries 101 INDUSTRIES BRASS LIGHTING HOME NICKEL FRANKFURT BRASS FRANKFURT NICKEL FRANKFURT STAINLESS FRANKFURT COPPER Jersey COPPER Dallas NICKEL Dallas STAINLESS Dallas COPPER I need to end up with a list like this company carries 101 INDUSTRIES brass LIGHTING HOME NICKEL FRANKFURT BRASS NICKEL STAINLESS COPPER Jersey COPPER Dallas NICKEL STAINLESS COPPER If it were just these few, I would insert some blanks and then transpose. But my list is over 30,000 rows with a few thousand different companies. I'm pretty new with this level of Excel and am about to bang my head with frustration. Can someone help? Please!! |
multiple ranges, transposing?
hi, !
are macros a valid alternate for this purpose ?... if so, copy/paste the following into a standar code module in the vba editor and run while "that" worksheets is the active sheet... Sub Transpose_CarriesByVendor() Application.ScreenUpdating = False Dim Start As Long, nCols As Byte, nRow As Integer With ActiveSheet Worksheets.Add(After:=Worksheets(.Index)).Name = "Transposed" .Range(.[a1], .[a65536].End(xlUp)).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=[a1], Unique:=True [a:a].Sort Key1:=[a2], Order1:=xlAscending, Header:=True For nRow = 2 To [a65536].End(xlUp).Row Start = Application.Match(Range("a" & nRow), .Range("a:a"), 0) nCols = Application.CountIf(.Range("a:a"), Range("a" & nRow)) If nCols 1 Then Range("b" & nRow).Resize(, nCols).Value = _ Application.Transpose(.Range("b" & Start).Resize(nCols).Value) Else: Range("b" & nRow) = .Range("b" & Start) End If Next End With End Sub hth, hector. __ OP __ I start with a list like this company carries 101 INDUSTRIES BRASS LIGHTING HOME NICKEL FRANKFURT BRASS FRANKFURT NICKEL FRANKFURT STAINLESS FRANKFURT COPPER Jersey COPPER Dallas NICKEL Dallas STAINLESS Dallas COPPER I need to end up with a list like this company carries 101 INDUSTRIES brass LIGHTING HOME NICKEL FRANKFURT BRASS NICKEL STAINLESS COPPER Jersey COPPER Dallas NICKEL STAINLESS COPPER If it were just these few, I would insert some blanks and then transpose. But my list is over 30,000 rows with a few thousand different companies. I'm pretty new with this level of Excel and am about to bang my head with frustration. Can someone help? Please!! |
multiple ranges, transposing?
Hi! Sir
I try your code but it is not work Thanks hardeep "Héctor Miguel" wrote: hi, ! are macros a valid alternate for this purpose ?... if so, copy/paste the following into a standar code module in the vba editor and run while "that" worksheets is the active sheet... Sub Transpose_CarriesByVendor() Application.ScreenUpdating = False Dim Start As Long, nCols As Byte, nRow As Integer With ActiveSheet Worksheets.Add(After:=Worksheets(.Index)).Name = "Transposed" .Range(.[a1], .[a65536].End(xlUp)).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=[a1], Unique:=True [a:a].Sort Key1:=[a2], Order1:=xlAscending, Header:=True For nRow = 2 To [a65536].End(xlUp).Row Start = Application.Match(Range("a" & nRow), .Range("a:a"), 0) nCols = Application.CountIf(.Range("a:a"), Range("a" & nRow)) If nCols 1 Then Range("b" & nRow).Resize(, nCols).Value = _ Application.Transpose(.Range("b" & Start).Resize(nCols).Value) Else: Range("b" & nRow) = .Range("b" & Start) End If Next End With End Sub hth, hector. __ OP __ I start with a list like this company carries 101 INDUSTRIES BRASS LIGHTING HOME NICKEL FRANKFURT BRASS FRANKFURT NICKEL FRANKFURT STAINLESS FRANKFURT COPPER Jersey COPPER Dallas NICKEL Dallas STAINLESS Dallas COPPER I need to end up with a list like this company carries 101 INDUSTRIES brass LIGHTING HOME NICKEL FRANKFURT BRASS NICKEL STAINLESS COPPER Jersey COPPER Dallas NICKEL STAINLESS COPPER If it were just these few, I would insert some blanks and then transpose. But my list is over 30,000 rows with a few thousand different companies. I'm pretty new with this level of Excel and am about to bang my head with frustration. Can someone help? Please!! |
multiple ranges, transposing?
|
multiple ranges, transposing?
hi, Herbert !
Excel 2003 No code, easy formulas. http://freefilehosting.net/download/41e93 working from excel 97 <vg how would the names technic perform (as OP commented)... " ... my list is over 30,000 rows with a few thousand different companies." -?- regards, hector. |
multiple ranges, transposing?
On Oct 31, 8:32*pm, "Héctor Miguel"
wrote: how would the names technic perform ... Hector: I expanded the list to 2000 rows and I could not detect any visual delay. Used Excel 2007 Vista HP Pavilion 9350 Random data and other tools are provided for your testing. Converted to Excel 2003: http://freefilehosting.net/download/41f17 |
multiple ranges, transposing?
hi, Herbert !
good example, thanks ! regards, hector. I expanded the list to 2000 rows and I could not detect any visual delay. Used Excel 2007 Vista HP Pavilion 9350 Random data and other tools are provided for your testing. Converted to Excel 2003: http://freefilehosting.net/download/41f17 |
multiple ranges, transposing?
Hector:
Here is a more ambitious implementation that allows for more variability in the source data. Excel 2007: Looks better, easier to expand. http://www.savefile.com/files/1865061 |
multiple ranges, transposing?
its Grt
Most ot the Most Appriciate Could you Please tell How do you do that bcoz i am not expert in excel Thanks In Advanc "Herbert Seidenberg" wrote: Excel 2003 No code, easy formulas. http://freefilehosting.net/download/41e93 |
multiple ranges, transposing?
Upload your Excel file
and tells us where you got stuck. |
multiple ranges, transposing?
Kindly find the Attached File http://www.savefile.com/files/1869249 Thanks hardeep kanwar "Herbert Seidenberg" wrote: Upload your Excel file and tells us where you got stuck. |
multiple ranges, transposing?
|
multiple ranges, transposing?
I understand all the things but what is BinA,BinB,Loc,Rept Thanks Hardeep kanwar "Herbert Seidenberg" wrote: Hardeep: Excel 2007 http://www.savefile.com/files/1869856 |
multiple ranges, transposing?
On Nov 3, 6:57*pm, Hardeep_kanwar
wrote: .... what is BinA,BinB,Loc,Rept These are the defined names for the helper rows/columns. Find them in the Name Manager. |
multiple ranges, transposing?
Great
"Herbert Seidenberg" wrote: On Nov 3, 6:57 pm, Hardeep_kanwar wrote: .... what is BinA,BinB,Loc,Rept These are the defined names for the helper rows/columns. Find them in the Name Manager. |
multiple ranges, transposing?
Sorry to disturb you again
Could you Please tell me why some headers have two Define Name i.e. Children+childrenname,Child_DOB+DOB,Propno+propnu, Prop_DOB+PropDOB. Is there any reason to Define Name these Columns twice with some changes. If i have to Make these type of report in another or different headers which Columns would i Define name twice. Thanks In Advance Hardeep Kanwar "Herbert Seidenberg" wrote: On Nov 3, 6:57 pm, Hardeep_kanwar wrote: .... what is BinA,BinB,Loc,Rept These are the defined names for the helper rows/columns. Find them in the Name Manager. |
multiple ranges, transposing?
Review naming rules in Help.
Some of the names I edited, shortened, or numbered still appeared in the Name Manager. Twice defined ranges cause no errors, but I cleaned them up. To avoid confusion, PropNu has been changed to Key. Notice that most defined names are on Master sheet. The new names I added are only on Slave sheet. Edited version at the same link. |
multiple ranges, transposing?
Thanks Sir
For Clearing my Doubts Thanks Again Hardeep kanwar "Herbert Seidenberg" wrote: Review naming rules in Help. Some of the names I edited, shortened, or numbered still appeared in the Name Manager. Twice defined ranges cause no errors, but I cleaned them up. To avoid confusion, PropNu has been changed to Key. Notice that most defined names are on Master sheet. The new names I added are only on Slave sheet. Edited version at the same link. |
multiple ranges, transposing?
Herbert, Hector thanks for your help. I am learning macros, but am still a
beginnner, so I used the formula method and it has worked well. At the end I get a brass c d e 101 INDUSTRIES brass LIGHTING HOME nickel FRANKFURT brass nickel stainless copper Jersey copper Dallas nickel stainless cooper brass Now I am trying to sort them into columns to clean them up. I want to end up with stainless brass silver copper nickel 101 INDUSTRIES brass LIGHTING HOME nickel FRANKFURT stainless brass copper nickel Jersey copper Dallas stainless brass copper nickel Now I am trying to use an IF formula to organize them. The formula I am using only seems to pick the selection from the beginning of the array. The statment i am using is =IF($b2:$e2="stainless", "stainless", "") What am I doing wrong? "Herbert Seidenberg" wrote: Excel 2003 No code, easy formulas. http://freefilehosting.net/download/41e93 |
multiple ranges, transposing?
Mea Culpa,
Reorganized version: http://www.savefile.com/files/1883361 You might also like the alternate method shown: Requires no formulas, no code, no helper columns, no conditional formatting and no defined names. Slight loss in presentation, but big gain in flexibility. |
| All times are GMT +1. The time now is 12:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com