LinkBack URL
About LinkBacks
Hi all,
I need some help manipulating some data. I have attached a picture with an example. The data i have is in the left hand side format and i need it to look like the right hand side format.
Any idea what i need to do?
Thank you!
Capture.JPG
Hi all,
I need some help manipulating some data. I have attached a picture with an example. The data i have is in the left hand side format and i need it to look like the right hand side format.
Any idea what i need to do?
Thank you!
Capture.JPG
Maybe i E2, copied down:
=IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$15),0),0)),"")
and in f2, copied across and down:
=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$15)/($A$2:$A$15=$E2),COLUMNS($F:F))),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn! Thank you so much for taking the time to reply to my post.
The formulae are working pretty well on a small scale but it's really struggling with the 39,000 entries on my spreadsheet..
I assume i might be better off asking in the VBA sectin?
The slow bit will be column E. As an alternative...
Copy Column A into column E
data/data tools/remove duplicates/cotinue with current selection/check column E only and then remove duplicates.
Use helper column C. This avoids Countif Function which is slower.
In C2 then drag down
=IF(A1<>A2,1,"")
In F2 then drag down
=IFERROR(INDEX($A$2:$A$20,AGGREGATE(15,6,ROW($A$2:$A$20)/($C$2:$C$20=1),ROWS($F$2:$F2))),"")
In G2 then drag across
=IFERROR(INDEX($B$2:$B$20,AGGREGATE(15,6,ROW($B$2:$B$20)/($A$2:$A$20=$F2),COLUMNS($G$2:G2))-ROW($A$2)+1),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks