matching data on different sheets

[lachk]

Hi all

I am sure this is very simple, but I have hit a brick wall.

I have been asked to do a comparison of sales figures year on year.

Some customers have dropped off between years and therefore I cannot do straight copies.

Is there a way of doing a look up function so I can look for a specific customer and then add figures from different cells in that row. I have to do this for over 2000 customers so don't want to do it manually. I have attached a small example in case I haven't been clear enough.

Sorry for the confusing post, but I have spent hours looking at this and seem to be banging my head against a wall now.

Many thanks in advance

Dave

[Marcol]

Try this updated workbook

Typical formula
=VLOOKUP($A5,'2009'!$A$2:$D$10,MATCH($B$2,'2009'!$A$2:$D$2))

Hope this helps

[arthurbr]

Hi and welcome to the board

You could also use a Pivot Table

Go to Data - Pivot Table - Select Multiple consolidation ranges - Make page fields for me
Select the different ranges and add them
Finish the wizard
Then drag the dropdown containing Item1... etc (age fields) to the table and place it as column fields ( it's all very intuitive don't worry)
If needed you can edit the Column headings and change them to whatever you like

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

EDITED THIS TIME

[lachk]

Try this updated workbook

Typical formula
=VLOOKUP($A5,'2009'!$A$2:$D$10,MATCH($B$2,'2009'!$A$2:$D$2))

Hope this helps

Thanks for your response, as the layout of my actual spreadsheet is a little different could you explain the breakdown of the formula so I can replicate it on my proper sheet please.

I would have attached the proper sheet, but I would get shot as it contains all my companies sales figures and posting them on a forum might cause me a few problems

Thanks

[Marcol]

Post a similar workbook to your original sample.

This time show the layout of each sheet as it is on your real file, and the sheet names are the actual sheet names you will be using.

Doing this will help in explaining how the formulae works.

[lachk]

I have worked around the details on my actual worksheet and changed all the names.

As you will see I have a lot of "customers" from 2009 that do not appear in 2010, this is what is causing me the problems.

I think the sheet is self explanitory, but possibly not as logic does not seem to be my strongpoint today.

Thanks again

[Marcol]

Try this workbook

Typical formula

Please Login or Register  to view this content.

Your data needs a bit more thought applied.

1/. There are numerous examples where on one sheet there is for example " 'Wallcoverings
" (leading apostrope) and in the sheet we have to compare this is "Wallcoverings "(trailing strings)

MATCH($C$2,'JUNE 2009 BY PROD CAT'!$B$3:$G$3,0)
looks for an exact match and returns, in this case, the relative column number in
$B$3:$G$3.

Headers on each sheet must match "Woodwork"on one, and "Woodcare" on another is not a good idea, match can find near misses, but what one?
Headers are best being unique within any table,

2/. To use VLOOKUP data must be sorted ascending in the column you are searching.

3/. Better to make a list of the customers you need to query Duplicates in any table will cause problems.

4/. You could simplify the references by using dynamic named ranges.

5/. Merged cells don't help, keep them for board-room presentation.

6/. You might be better using Pivot Tables.

I have "cleaned" enough of your sample workbook to make it work.

Hope this helps

[lachk]

Thanks for the time involved in looking at this for me, but I think I am way out of my depth. I cannot get your formula to work in my workbook and the only thing I changed was the customer names.

I am not sure what I am doing wrong and to be honest I am a little confused by your explanation (my lack of knowledge again I'm afraid)

I will soldier on and keep trying, but I don't hold out much hope.

[Marcol]

After you change the customer names, you must sort the sheet on these names (ascending)

2/. To use VLOOKUP data must be sorted ascending in the column you are searching.

If you are not working with the sample workbook from post #8, check your data for the inconsistancies I described above.

[EDIT]
Look at the formulae in Columns C,D,F,G,I,J,L, and M
They are all different.
Perhaps your difficulty is arising because I only mentioned a typical example in my explanation.

[lachk]

Thanks for your patience, I think I am nearly there, just one last problem.....

Where I have a new account opened this year there are obviously no 2009 results, The formula returns #N/A..

Is there anyway I can put something in place to change any cell that shows #N/A to show 0 instead?

Thanks yet again

EDIT: I know about the replace function, I was wondering if there is something that would do it automatically if a new account was added down the line.

[Marcol]

I don't know why you get #N/A with the given formulae.

You should get incorrect data, i.e. the nearest match that VLOOKUP can find, and that is even worse!

I'll look into it.
I might take a while to reply, I have other commitments today.

[lachk]

I added "FALSE" to the formula so I didn't get the wrong results in the cells, but this is returning #N/A which is better than the wrong results, it just looks a bit scruffy.

I have amended the page set up so the #N/As don't show up in a print, but I just wanted it to look pretty on screen

[Marcol]

Okay.

N.B.
I have just noticed that the sample you provided in Post #7 has hidden rows that contain data that might be considered sensitive. I suggest you remove this attachment.

I have removed that data from the attachment in Post #8