I am not quite sure how to transpose multiple values in a massive spreadsheet. The output is vertical in a table format with unique products per line mapped to an ID. I need to achieve unique products each in their own column per ID. Please see attachment.
Thanks in advance for your help.
See attachment.....
Quang PT
In the attached file I've assumed that your IDs are not just sequential numbers in reality, so to avoid confusion I've just changed them to letters. I've used two helper columns (D and E) which are coloured blue on a pale yellow background, and put this formula in cell D4:
=IF(COUNTIF(B$4:B4,B4)=1,MAX(D$3:D3)+1,"-")
and this in E4:
=IF(B4="","",B4&"_"&COUNTIF(B$4:B4,B4))
These can be copied down as far as you like (I've copied to row 20 in the example file) - the first formula helps to identify each unique ID and the second helps to establish each parent_child. To get the unique IDs, you can use this formula in cell G4:
=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),D:D,0)),"")
You can use this formula in H4 to get the first product for an ID:
=IFERROR(INDEX($C:$C,MATCH($G4&"_"&COLUMNS($H:H),$E:$E,0)),"")
which can be copied across for as many products as you are likely to encounter. These formulae can also be copied down as far as you need them (I've copied to row 13 in the example file).
You can apply the formatting to the table after you are happy that everything is working as it should.
Hope this helps.
Pete
a VBA solution
run with {ctrl} + t in attached file
Sub TransposeInventory() Dim IDrng As Range, ID As Range, output As Range, c As Long, r As Long Set IDrng = Range("B4", Range("B" & Cells.Rows.Count).End(xlUp)) Set output = Range("K3") For Each ID In IDrng If ID = idprevious Then r = r c = c + 1 Else r = r + 1: c = 0 End If idprevious = ID output.Offset(r, c) = ID.Offset(, 1) Next ID End Sub
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Thanks bebo, Pete and kev. Pete, I first saw your reply and it almost works, but somehow is matching and returning data from incorrect IDs. I am not sure exactly what's wrong...so close. bebo, your solution is a couple less steps, although I have forgotten how to get the array formula to execute. kev, I wanted to hold off on adding a script unless it is my last resort, but I appreciate your reply as well.
Ok, I found the array initial execution with CTRL+SHIFT+ENTER, however, how to paste it down / fill down with it?
Thank you all sincerely.
Last edited by Seattlites; 02-20-2017 at 01:59 PM.
You can see that my solution works in the sample file, so there must be something different in your real file - perhaps you can attach an extract of it which shows where it is going wrong.
As for copying array formulae, you can just use your usual method(s) - there are many ways.
Hope this helps.
Pete
Thanks Pete. Please see attached.
I suggest you use this formula in cell F2:
=IFERROR(INDEX($T:$T,MATCH($E2&"_"&COLUMNS($F:F),$C:$C,0)),"")
then you can copy this across and down as required.
Instead of the full-column references (which always start on row 1), you had $C1:$C3042, and as this is copied down the references change on each row as you did not have a $ symbol before the row references.
Hope this helps.
Pete
Thanks again Pete. I normally attempt to reduce the lookup Excel has to perform to less than an entire column when using anything more powerful than VLOOKUP. Even my i7 quad core 16GB machine has trouble churning through INDEX + MATCH functions when applied to thousands of rows.
In any case, I transposed the location of that absolute reference so thank you for finding that error in my execution.
Regards,
Steve
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks