PDA

View Full Version : Transform data from vertical to horizontal



YasserKhalil
03-07-2017, 03:41 PM
Hello everyone
I have a code that combines data based on ID and it worked well .. but I need to get the results in another way if possible
Attachment illustrated the issue better than description
Thanks advanced for help

mancubus
03-07-2017, 11:30 PM
one way is:


Sub vbax_58813()

Dim i As Long
Dim arr, e

arr = Range("A1").CurrentRegion

With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(arr)
If Not .Exists(arr(i, 1)) Then
.Item(arr(i, 1)) = arr(i, 2)
Else
.Item(arr(i, 1)) = Join(Array(.Item(arr(i, 1)), arr(i, 2)), "~")
End If
Next i

i = 1
For Each e In .Keys
Cells(i, 7) = e
Cells(i, 8) = .Item(e)
i = i + 1
Next
End With

Range("G1").CurrentRegion.Columns(2).TextToColumns _
Destination:=Range("H1"), DataType:=xlDelimited, Other:=True, OtherChar:="~"

End Sub

snb
03-08-2017, 03:37 AM
crossposted at: http://www.eileenslounge.com/viewtopic.php?f=27&t=26238&sid=df14c88e34cebaad45628903f521817a

YasserKhalil
03-08-2017, 04:49 AM
Thanks a lot for great help and I am so sorry for not putting the other link. I forgot at all about that as I was in hurry yesterday
Best Regards

jolivanes
03-08-2017, 07:32 PM
I am naturally suspicious when someone with quite a few posts says "I forgot"
Just a quick check for your posts:
vbaexpress 322
eileenslounge 644
mrexcel 650
excelforum 4443
and probably a lot in other forums yet.
So after 6000+ posts, you forgot to mention that one should let the helpers know that it was also posted in another forum!


Studying to be a politician maybe?

YasserKhalil
03-08-2017, 08:54 PM
Mr. jolivanes I really forgot because the time I posted the threads was late and I was in hurry ... And thanks for your reply

snb
03-09-2017, 01:09 AM
You can still restore your omission by adding links in this thread to all places where you asked the same question.

YasserKhalil
03-09-2017, 04:58 AM
I think you have already done that Mr. snb
Generally
http://www.eileenslounge.com/viewtopic.php?f=27&t=26238&sid=df14c88e34cebaad45628903f521817a

I don't know if I have to apologize again and again ...for everyone for something I forgot to do :(

mancubus
03-09-2017, 06:53 AM
when jolivanes pointed your memberships in other forums, snb (and i) thought you opened the same thread in those forums as well. but it seems you have two threads; one here and one in eileen.

YasserKhalil
03-09-2017, 09:06 AM
Yes Mr. mancubus (http://www.vbaexpress.com/forum/member.php?37987-mancubus)
Just in these two forums.. I usually put links if I posted in two forums but this time I really forgot about it as it was late in my country and I had to finish up my work quickly so I posted in both forums without paying attention to put the links
Regards

apo
03-13-2017, 06:04 AM
I am with jolivanes in post #5.. where i come from.. that's called bul****!

YasserKhalil
03-14-2017, 09:51 AM
Thanks Mr. apo