Replacing and adding data to columns with functions

[void_5]

Hello, my question is somewhat difficult to describe... so I'm sorry about the poor title.

Example:
Example.jpg

Let's say that we have a certain item and the sales on the item is being reported for each week. But in the second week, new items were added to the list in addition to the old ones. I want to add the new values sales to the week 2 column for all items that exist in week 1 and then add any items now in week 1 to the week 2 column. In a more algorithmic sense:

1. If the item name for week 2 = item name in week 1 >> add new value to week 2 column
2. If the item name for week 2 =/= item name in week 1 >> add item name to item name column and leave week 1 column blank

This is obviously easy to do manually in the example but I am dealing with > 500 items so I can't do it by hand. I am not familiar with all the Excel functions but if you give me the name of the function to use and general idea of the steps I can figure out the rest.

Please let me know if this was confusing and I'll try to find a different way to explain my problem.

Thank you!

[daffodil11]

Simply grab all of your row headers and put them in a single column, and then use the Data->Remove Duplicates tool to create your single list.

Then, use INDEX(..MATCH(.. or SUMPRODUCT to pull in the values needed.

If you could attach a sample workbook, I can provide the actual formulas.

[void_5]

Hello, thank you for your response! It took me a bit of searching but you pointed me in the right direction. Part of the problem was not knowing what was available or possible but I knew I could figure it out if I had the basic idea.

After putting all the row headers in a single column, I originally used this formula (here my cell/columns label refer to my personal spreadsheet and not the example image I posted):

=INDEX($G$2:$G$186,MATCH(A2,$F$2:$F$186))

Which worked except that in the cases where something in column A was not found in column F, it would duplicate the previous value where there was a match between cells compared in column A and column F. In other words if it tried to match but didn't find anything it would just duplicate the value of the last successful match. This seems like a very strange response to the the command so I'm not sure what was going on. Any insight on this would be appreciated. What I actually wanted in these cases was just a blank cell, indicating that the there was simply no value in this column for that particular row. So I did this instead:

=VLOOKUP(A2,$F$2:$G$186,2,FALSE)

Which maybe could have been done with INDEX and MATCH in a similar way since it seems INDEX is a more versatile version of VLOOKUP. But then the problem was that the cells in column A not in F (as indicated above) showed "N/A". So it get rid of these I did:

=IF(ISNA(VLOOKUP(A2,$F$2:$G$186,2,FALSE))," ",VLOOKUP(A2,$F$2:$G$186,2,FALSE))

Which seems to have solved all my problems! So thank you very much for putting me in the direction. Hopefuly someone else will come across this in the future and find it useful.