Transpose multiple text-based columns to single column while retaining record ids
Hello,
I am new to this service. I am hoping someone can help me with an issue I have been struggling with.
Attached is a sample dataset. Columns D through AG need to be arranged as individual records, but columns A through C need to follow each record as each row (as currently organized) is an event. I have tried all kinds of functions (transpose, index, match, offset, etc.), but this appears to be over my head.
Can someone help, please?
Thanks in advance!
SchoobsVT,
Welcome to the Excel Forum.
Can you add to your workbook a new worksheet with the new format you are looking for (say, for at least two records)?
Click on the New Post button, then scroll down and see Manage Attachments.
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
run the macro and it will reformat and place the data on sheet2
click on the * Add Reputation if this was useful or entertaining.
This should do it, it's not fast, but it does the work:
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
tony h, you are a life saver! That bit of VBA is magical!
Quick follow-up question: Other than Excel's column and row limits, does this macro have any limits? For example, if I have 1000 rows (instead of just over 500) and 40 columns (instead of just over 30), will it still work, or do those parameters have to be set? Please excuse my lack of familiarity with VBA.
It works on the size of the used area of sheet1. So it readjusts to what you have got.
The only issue might be if your rows x columns was greater than the maximum size of a sheet.
PS the best way to say thanks is to click on the "scales" icon (next to the red triangle) on any post you find helpful.
i think its better to store the data in a variable, do the work and paste back onto sheet or you will be slowed down by Excel/ VBA interaction. If you have huge amounts of rows and columns its worth keeping this in mind.
See attached using this technique.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks