multiple ranges, transposing?

[Mea]

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!!

 

[Héctor Miguel]

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 __

 

[Hardeep_kanwar]

Hi! Sir

I try your code but it is not work

Thanks

hardeep

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!!

 

[Herbert Seidenberg]

Excel 2003
No code, easy formulas.
http://freefilehosting.net/download/41e93

 

[Héctor Miguel]

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.

 

[Herbert Seidenberg]

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

 

[Héctor Miguel]

hi, Herbert !

good example, thanks !

regards,
hector.

 

[Herbert Seidenberg]

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

 

[Hardeep_kanwar]

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

(e-mail address removed)

 

[Herbert Seidenberg]

Upload your Excel file
and tells us where you got stuck.

 

[Hardeep_kanwar]

Kindly find the Attached File

http://www.savefile.com/files/1869249

Thanks

hardeep kanwar

 

[Herbert Seidenberg]

Hardeep:
Excel 2007
http://www.savefile.com/files/1869856

 

[Hardeep_kanwar]

I understand all the things but what is BinA,BinB,Loc,Rept

Thanks

Hardeep kanwar

 

[Herbert Seidenberg]

On Nov 3, 6:57 pm, Hardeep_kanwar
.... what is BinA,BinB,Loc,Rept

These are the defined names for the helper rows/columns.
Find them in the Name Manager.

 

[Hardeep_kanwar]

Great

 

[Hardeep_kanwar]

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]

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.

 

[Hardeep_kanwar]

Thanks Sir
For Clearing my Doubts

Thanks Again

Hardeep kanwar

 

[Mea]

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]

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.