Match items in two ranked lists

**jim e.** · 09-23-2009, 12:59 PM

I want to sort and match items in a ranked list without losing their ranking. I have attached a picture of the first 20 rows of data.

Column A is the frequency ranking for a list of words in Column C
Column E is the frequency ranking for a list of words in Column D

I want to learn how to sort columns C and D (the words) so I can see identical words matched up next to each other, keeping the correct ranking in the column next to each word. e.g., I want Excel to show me by a sort which items in one list have an identical partner in the other list.

jim e.

**NBVC** · 09-23-2009, 01:03 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**jim e.** · 09-30-2009, 01:28 PM

Thank you for the tip. I have added an illustration to the original message above--jpg image.

**NBVC** · 09-30-2009, 01:46 PM

Try attaching Excel file.. so we don't have to do the unnecessary work of recreating your table.

**jim e.** · 10-01-2009, 08:39 AM

Done. Thx.

**NBVC** · 10-01-2009, 09:14 AM

See attached...

You cannot sort in place to get your interpreted desired results...(maybe with VBA... but not sure if necessary.. and better left to someone else to do, rather than I)...

You can add formula in F4, =C4 to get same data as column C in the same order, then use formula next to it in G4: =SUMIF(D:D,F2,E:E)

based on assumption that there are no duplicates.. if there are, you will get sum of duplicates.

Note: Your column D has many extra invisible characters like space and Ascii code 160.. they need to be removed... I removed them in the attached.

If you need to remove on another file.. do and Edit|Replace of Space with nothing and then repeat replacing 0160 (holding ALT Key down and entering with number keypad on right of keyboard) and replace with nothing.