Merge data of cells which have a similar "another cell value" like a primary key

S

sorabh

Hi friends
i need some help on this one, say i have file which has the followin
format .

Tool User
---------------
Axe A
Gun A
Bullet A
Gun B
Baloon B
Jackham B
Wood C

so is there any way in excel in which i can have a file which
has:

Tool l User
------------------------- -----
Axe,Gun,Bullet A
Gun,Baloon,Jackham B
Wood C

How will i do this ?
Any delimiter will do( i have used a comma here)
Please revert
Thank s in advance ;)
sorab
 
M

Mike

Sorabh,

The easist way is with a pivot table. If you don't know
how to use a pivot table you might read some of the Excel
help screens that explain how to use them. Pivot tables
can be difficult but, with what you are trying to do, it
will be a very easy pivot table.

Mike
 
D

Dave Peterson

I don't think a pivottable will help in this case. Pivottables are used to
summarize numbers/quantities/counts. And Sorabh is trying to mess around with
text.

(You're absolutely correct about how powerful they are. After an hour of
playing around, you'll wonder how you did any summarizing without them!)

But if you want to learn more about pivottables, here are a few links.

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
D

Dave Peterson

How about a macro:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With newWks.Range("a1").Resize(1, 2)
.Value = Array("Tool", "User")
.Font.Bold = True
End With

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("a:b").Sort _
key1:=.Range("b1"), order1:=xlAscending, _
key2:=.Range("a1"), order2:=xlAscending, _
header:=xlYes, MatchCase:=False, Orientation:=xlSortRows

oRow = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, 2).Value <> .Cells(iRow - 1, "B").Value Then
oRow = oRow + 1
newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
newWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
Else
newWks.Cells(oRow, "A").Value _
= newWks.Cells(oRow, "A").Value _
& ", " & .Cells(iRow, "A").Value
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top