Re-arranging data

[yorkuvir]

Hello folks,

https://ibb.co/kZZ2Qv

As in the above figure, I have a worksheet containing rows that looks like the left side of the figure, and I wish to sort them out to look like the right side of the figure. Please note, the Goods referred to in the subsequent rows are different, say, apples and oranges respectively, so I cannot merge them or apply filters to them.

What is the shortest way I can accomplish this?

Thanks.

 

[Oeldere]

Since you add an picture I can't show you, but did you consider using Pivot Table.

 

[yorkuvir]

@mole999 : Their scores would indeed be marked as 0, as in D7. Absentees would be left blank as in D10. [Ref: Picture https://prnt.sc/fcy79z ]


@Oeldere : Rick's path is perfect for me, without a need for considering pivot tables.


I am waiting for Rick Rothstein's reply. Thanks.

 

[yorkuvir]

Anyone ready to take this? Thanks....

 

[AliGW]

Cross-posted here: https://www.excelforum.com/excel-general/1187265-re-arranging-data.html

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[yorkuvir]

@Joe4:

Thanks for pointing me towards the Forum Rules. I will mention any cross-posting in the future if and when a situation arises.

My reason for cross-posting was that I was in a little urgency to see the solution at the earliest. However, as of now, I am not in need of a solution for the same, since I have chosen a completely different approach to the original problem. So it doesn't matter to me now whether anybody is responding to this thread or not. But my sincere thanks to Mr. Rick Rothstein for initiating an elegant solution.

I just thought it was easy to modify Rick Rothstein's solution and that the reason nobody had since replied was that this thread didn't get proper notice. It never occurred to me that people might 'waste time working on a question that may already be answered'.

Anyway, although it may not be relevant to me now, if anybody can come with a solution for my question, it might be useful for those who seek similar solutions in the future.

Apart from this, I wonder why attachments are not allowed in this forum. In my opinion, if an Excel file can pose a security risk, at least uploading pictures can be allowed, if this Forum cannot recommend a third-party image-hosting site. [I know at least 2 other great forums where MS Excel files are accepted as attachments - even with macros in them.]

Thanks.

 

[AliGW]

The lack of attachments here is what sets this place apart - and I find it equally baffling. Nonetheless, the powers that be insist that they will not allow it, and they don't seem to like anyone questioning it, as I have found to my cost, so we have to use other means. One such is the Excel ForumTools add-in (link in my signature line below), with which you can reproduce sections of your spreadsheet with formulae. For VBA code, just copy it to your post and then add code tags (# symbol on the toolbar above) around it. I hope this helps!

 

[yorkuvir]

@AliGW :

Thanks for kindly letting me know. I will follow your above advice hereafter.

 

[yorkuvir]

For anyone interested, the problem has been solved as per https://www.excelforum.com/excel-general/1187265-re-arranging-data.html#post4666542 .

This is my last post in this thread regarding this problem.

Thanks.