LinkBack URL
About LinkBacks
Any idea how can I achieve this layout result?
Maximum sequence will be 7 columns.
Need your support on this with same formula or easy way to do it (not VBA) since I have around 20k of results to transpose like sample attached
Capture.PNG
Any idea how can I achieve this layout result?
Maximum sequence will be 7 columns.
Need your support on this with same formula or easy way to do it (not VBA) since I have around 20k of results to transpose like sample attached
Capture.PNG
Last edited by sepmir1; 05-11-2017 at 07:57 AM.
I always use VBA for this. I've attached a workbook you can use. Drop your data into columns A:C, it's important your column B have those sequence numbers as we are using those to determine how big the output array needs to be. Click the button and it will give you an new output sheet in your consolidated format.
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!)
Million thanks JBeaucaire,
Works perfectly!!! Tkx
Unfortunately I am not a VBA wizard and situation likes this comes to me in regular basis.
Quick question: if I want more than 3 columns which variable should I change in code?
Additionally… Do you know how to do it with formulas
Once again, thank you for your support.
Don't understand the ramifications of the "quick question".
Additionally, no, I use VBA for this.
sepmir1 welcome to the forum.
I'd be happy to work a formula solution. If you upload a brief sample workbook (rather than screen shot) it will save having to retype the data. About 30 rows should be enough.Additionally… Do you know how to do it with formulas
If you are not familiar with how to do this:
To attach a file to your post,
- click “Go Advanced” (next to Post Quick Reply – bottom right),
- scroll down until you see “Manage Attachments”, click that,
- click “Browse”.
- select your file(s)
- click “Upload”
- click “Close window”
- click “Submit Reply”
- be sure to desensitize the data
The file name will appear at the bottom of your reply.
Dave
We are from diferent countries so "quick question" in PT we assume... "easy answer".
No worries mate I understand should be easy thru VBA.
Tkx
Thank you FlameRetired
Attached you can found a sample file.
Although the unique IDs can be returned by formula the simplest way is to copy / paste IDs from column A to the output range (column F?) then remove duplicates.
That is what I have done in the attached. Then array enter this formula in G3 fill down and across until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:
Please Login or Register to view this content.
Although the unique IDs can be returned by formula the simplest way is to copy / paste IDs from column A to the output range (column F?) then remove duplicates.
That is what I have done in the attached. Then array enter this formula in G3 fill down and across until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:Please Login or Register to view this content.
Edit I just re read post #1.This solution might not be so good. Array formulas are resource hungry and that is a lot of rows. I will keep working on alternate solution.I have around 20k of results to transpose like sample attached
This version should be an improvement. Please let me know how it does on your real data.
This does not have to be array entered.Formula:Please Login or Register to view this content.
I'm confused. The data given in the file from post #7 would work perfectly if pasted into the VBA workbook I provided, one button. Just copy the data only and paste into row 2 downward. When you try it, are the results not as expected?
I made one small tweak to correct the output title row.
Don't be confused JBeaucaire.
Your solution works perfectly. Better would be impossible.
However, since I'm not an experienced VBA user I am looking for conventional alternatives using excel formulas.
FlameRetired is available to waste some time seeking a solution through formulas, which I appreciate.
FlameRetired - you are the one! This solution is outstanding!!!Originally Posted by FlameRetired
This version should be an improvement. Please let me know how it does on your real data.
This does not have to be array entered.Formula:Please Login or Register to view this content.
In fact JBeaucaire code is easier than yours but your tip also fix my problem and works like a charm
Of course need some same changes but those I can do from here.
Million thanks for your support and dedication.
JBeaucaire,
What a fantastic solution. All results as expected.
FlameRetired did some tuning in to initial formula and touch the point: was exactly what I need.
You have the best solution but VBA is not my expertize.
Thank you for your support.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
JBeaucaire,
I'm getting a Run-time error '9':
Subscript out of Range
I've attached a sample dataset with 3 items. There will be over 100 items.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks