Merge items having similar row values ?

  • Thread starter Thread starter sorabh
  • Start date Start date
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
 
Hi Dave,
thanx for the reply, actually the group " excel misc" in which i ha
post the original message didnt have any replies "0 replies", hence
had post the same problem in this group, but as per ur link i se
people have come up with solutions.
ofcourse a pivot table wont work( i know about it and have use
extensively but only for summarizing numeral values )
your macro must work i believe, i havent done coding in excel but hav
done in VB so i am making sense of the code, only how to put it i
excel is a concern but i think the link u gave will help.
only dave can u put some comments in it as well, although thanks a to
for taking the time to write the whole code.
I will try to replace the values with my real table hope it works.
hey dave what if i upload the file and let u know what exactly i wan
then would u be able to replace the exact columns in ur macro code. an
upload the same file so i can have it.
though its asking for too much what do u say ?
thnx
sorab
 
I'm one of the people who won't open attachments. And I think you'll be able to
lots more if you try it yourself, too. Post back if you have trouble.

As for comments, just a couple.

I dimmed some varibles to represent the current worksheet (you can change the
name in the Set line:

Set curWks = Worksheets("sheet1")

I added a new worksheet.
Put some stuff in A1 and B1 (.resize(1,2) means make that original range
(.range("a1")) look like 1 row and two columns.

find the firstrow (2) assumes you have header on the worksheet
find the lastrow. I used column A. Started in A65536 and came up the column
until I hit a value.

Then sorted that data in A:B.

Then I worked down the list.

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

if the value in row X is different from the value in row X-1, then write it out
to the new worksheet.

If they're the same, then plop the value at the end of the existing value.





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
 
Hey DAVE !;) your code worked like anything
really man
thanks a ton i figured out the variables and they are working fine wit
my doc !!
may god bless u with more knowledge to help lesser mortals like us.
thnks a ton
cya soon with more problems
sorab
 
gld it wrkd.



sorabh < said:
Hey DAVE !;) your code worked like anything
really man
thanks a ton i figured out the variables and they are working fine with
my doc !!
may god bless u with more knowledge to help lesser mortals like us.
thnks a ton
cya soon with more problems
sorabh
Click to expand...
 
Back
Top