Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

if i have a workbook filled with data like this example below here

   A      B      C
1 test   edge   blue
2 test   edge   red
3 dust   daf    grey
4 dust   daf    red

And i want to clean this up so it says

   A      B      C     D
1 test   edge   blue  red
2 dust   daf    grey  red

anyone can help how to do this ?

share|improve this question
Do you have access to Unix, Linux, a Unix-like system, Cygwin, or some other Unix look-alike? If you do, the easiest solution might be to export your data to a CSV file, process it in awk, and then re-import it to Excel. – Scott Dec 18 '12 at 20:40
If in your example the fourth column were say w, x, y ,z what would be the result you want? – pnuts Dec 19 '12 at 0:26

Sort by A then B, then put into D1:


Fill Down column D, then either copy and paste as values or filter out the blanks in column D. That should work if your data is all like the above.

share|improve this answer
it have to be dynamic, 10 fields cold be the same in A anb B i dont know what the data will be before i get the sheet. – Darkmage Dec 18 '12 at 14:02
Well, in that case add more columns: E1 will have =IF(AND(A1=A3,B1=B3),C3,""), and so on. – benshepherd Dec 18 '12 at 14:05
i cant do this manual the sheet might have 10000 lines, and hundred of collums, i need a macro or somthing. this must be a problem that has been solved before. – Darkmage Dec 18 '12 at 14:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .