2

I have two sets of data from two years as below which needed to be sorted by product and row. Eg. I need A2 and its product sales on the same row for both years.I have had a look at the the solution here but don’t know how to adjust it.

    2012                 2011   
Product   Sales     Product  Sales
A1          5       A1         3
A2          7       A4         5
A3          2       A6         3
A4          5       A7         2
A5          3       A9         4
A6          4       A10        5
A7          3           
A8          4           
A9          5           
A10         6

This is the desired result which has the same product on each row for both years.

    2012                 2011   
Product   Sales     Product  Sales
A1          5       A1         3
A2          7           
A3          2           
A4          5       A4         5
A5          3       
A6          4       A6         3
A7          3       A7         2
A8          4           
A9          5       A9         4
A10         6       A10        5

My actual database has over 800 rows over 8 years so performing this task manually is too time consuming. Is there any efficient way of doing this?

Help is much appreciated.

Community
  • 1
LogiKal
  • 75

1 Answers1

1

I would recommend you to use VLOOKUP function.

The syntax for vlookup is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) where

  • lookup_value is what you're trying to match, it can be a variable or a constant
  • table_array is the table you are searching
  • col_index_num is the column (starting with 1) of the table in which you are searching for the lookup_value
  • range_lookup is whether you want it to return exact matches only; true or false

VLOOKUP result

Fill H2 with the formula =VLOOKUP(G2;A:B;2;0) and copy until H11.

Fill I2 with the formula =IFERROR(VLOOKUP(G2;C:D;2;0);0) and copy until I11.

IFERROR is necessary because when the formula is looking for tha value A2 for example it will not find it, so, we replace the error with 0.

I think it is self explainable and make you able to figure out how to do this for the others columns.

Math
  • 350
  • Sorry for the multiple edits.. I believe my post is cohesive now. – Math Jul 16 '13 at 14:45
  • Hi, thanks for the reply. However, when I enter the formula into the cell I get an error. This is an image of the error. – LogiKal Jul 16 '13 at 15:54
  • Well, there's a configuration in Excel that determines the delimiter for more than one parameter in the formula, in my case is the semicolon (;), maybe for you is the comma (,). Switch the ; by , and let me know if it works for you. – Math Jul 16 '13 at 17:11