Sorting Data quickly

[kohno71]

Hey guys, first time here!

So I've been tasked to sort out a whole lot of data. The data comes in 2 columns and different amount of rows for each data set. The first column contains the category ex.(name, phone number, company name, etc) and the second column contains the relevant information. As a result, there is one table for each person. I want to sort this data better. I have attached an excel workout, with the first sheet containing data and the second sheet containing my desired result. If there is a way to make it happen like my desired result faster, it would be very helpful.

More importantly, if you can describe the steps, it will be the most helpful. I've tried pivot table and vlookup but I'm afraid my excel knowledge is too limited to make good use of those methods.

Thanks for your time and help people!

[MarvinP]

Hi kohno,

See the attached which will convert your data to your desired result.

Index Match multiple Columns to table.xlsx

[TMS]

@Marvin: sadly, it doesn't work properly as the number of properties for each fruit varies. Hence, Orange is not Round (allthough, obviously it is, a bit) and from China.

[MarvinP]

Hey TMS,

You are absolutely correct. My answer above was bogus or simply wrong.

See if this attached answer works now. I learned how to Fill Down a few days ago using the method in the attached. I never thought it was possible without Power Query.

Index Match multiple Columns to table Fixed.xlsx

[TMS]

@MarvinP: thanks for the rep. Good try, both times ... but not quite got the prize.

Looking at it on an iPad so I can't see how it's done.

[MarvinP]

I hope this second try works. You need to manually type in the green areas. I could have done a DNR to span the whole table but simply made it down to row 100 in the formula. I'm thinking the iPad should give the same answer as my pc.

[TMS]

Looks good except you have an extra Shape column ... proving the point, maybe?

[kohno71]

Hey MarvinP and TMS,

Thanks a lot for your help guys, you are simply the best! Cheers~~~ :D

[TMS]

MarvinP provided the solution ... I’m just running interference



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.