Hello all,
I'm wondering if this is possible. Here is a sample of a large data set:
Capture.PNG
I want to transpose these multiple rows into one row per service, id, name, age, street address with multiple phone numbers like this:
Capture1.PNG
Thanks guys!
Last edited by Luden; 09-13-2019 at 09:16 PM.
Is your first output data line correct? You included a Sprint call (third data line of original data) in with the surrounding Verizon calls.
Where a name like say John has two or perhaps more Service references is it always the first one that you want to record?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Oh, that was my mistake. The service column should not be included in this excel file. Just id, name, age, street address and phone numbers. Sorry about that.
Is the ID sufficient to identify identical accounts or could the same ID have different names, ages and/or street addresses (which, if so, would require individual output data lines I guess)?
The same ID could have different names, ages and/or street addresses.Originally Posted by Rick Rothstein
So then, would they require individual data output lines for each that differs in some way?
Also, will the data be sorted so that any differences are all one under the other?
It would help if your example showed the kind of differences we are now talking about and the output you expect for them rather than giving us a idealized example file hoping we will figure out how to handle what you have not shown or told us about.
Last edited by Rick Rothstein; 09-13-2019 at 06:54 PM.
Sorry about the inconvenience. Answers for both questions are YES. Here are new images:
Attachment 641371
Attachment 641372
Not sure if that's clear to you. This is my first time asking questions lol
Something seems to be wrong with your links... I get an "invalid attachment" message when I click either one. Did you use the same method to attach them as you did in your original message? I am guessing not as they look different than your first attachment's link (no surrounding "Attached Files" box).
Oops. Mine works fine. I don't know why. Anyway, this is the result table. Phone numbers can be many. If the names are different, even though they have the same ID, they should still be in two rows.
ID Name Age Street Address Phone Number
110 Jack Age 50 1402 Kendall St, Covington, KY, 41011 (514) 580-1873 (635) 523-5494 (487) 890-0766 (673) 552-8840 (937) 779-3649
110 Mary Age 52 1402 Kendall St, Covington, KY, 41011 (297) 653-9111 (773) 948-1349 (945) 965-7600 (226) 970-0616 (948) 560-6552
112 John Age 45 16 Maplewood Ln, Natchez, MS, 39120 (575) 236-9508 (978) 507-9294 (886) 527-7310 (899) 465-3198 (765) 777-8377
Attached is what I'd originally put together based in your original data.
Before I modify it to account for the dropping of the Service Ref would you upload a new workbook as you did erlier which clearly shows the original data and the results.
Currently the macro is
Please Login or Register to view this content.
Yes. I updated the excel in my original post. Thank you!
Try
Please Login or Register to view this content.
Based on your updated file at the link in Message #1, here is the macro that I came up with (it assumes your data has less than about 65500 or so rows in it)...
Note: If there is any chance this code will be run when no multiple phone numbers exist for any account, then some error trapping code would have to be added to the above code.Please Login or Register to view this content.
Last edited by Rick Rothstein; 09-13-2019 at 11:59 PM.
The problem is solved. Thank you guys for all your efforts. I really appreciate it!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks