vlookup? what adam did on the 11/11/05?!

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
Hi this is my first post I've had a look round and wow I never knew you could do so much with excel! I'm definately gonna be back I'm considered good at excel at my company but if I could learn 10 of the stuff on here I would be a legend to them all lol

Anyway I can't find an answer to this problem anywhere I'm sure its simple but its got me stumped!

I've got a table structured like this (sorry I will learn proper way to post tables soon but it is late):
A----------B----------C
Adam---10/11/05---12
Adam---11/11/05---6
Adam---12/11/05---4
Adam---13/11/05---3
John----10/11/05---67
John----12/11/05---4
John----13/11/05---3
Jack----10/11/05---9
Jack----11/11/05---1
Jack----12/11/05---2
Jack----13/11/05---45

What I want is to have a sheet for each of Adam, John and Jack that shows there data. I have cell A1 that holds there name and the A3 onwards has the date and I want B3 onwards to hold the numbers from column C above that relate to the correct day and person.

This is a simplified version of what I want but if I can get this working I think I can get what I want to work.

Sorry if this is really simple but please post an answer it would really help me!

Oh by the way the dates are not always continous as you can see from john if he didnt sell anything one day then nothing appears. Also the sheets have all days down the left hand side and all sheets show the same dates ie 01/11/05-30/11/05.

Many thanks in advance
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi

In B3 enter the formula
Code:
=SUMPRODUCT(--(Sheet1!$A$1:$A$11=$A$1),--(Sheet1!$B$1:$B$11=A3),(Sheet1!$C$1:$C$11))

Adjust the ranges in Sheet1 to suit. Copy down as required.


HTH

Tony
 
Upvote 0
That is awesome little tweaking and it works a treat! Out of interest what does the -- do? Think I understand how it works otherwise :)

Thank you you've really helped me out!
 
Upvote 0
Hi

The -- converts the boolean value of True / False to a number. The first - will make it negative, the second will convert it back to positive. True will come out as 1 and False as 0.


Tony
 
Upvote 0
Sethrow said:
Hi this is my first post I've had a look round and wow I never knew you could do so much with excel! I'm definately gonna be back I'm considered good at excel at my company but if I could learn 10 of the stuff on here I would be a legend to them all lol

Anyway I can't find an answer to this problem anywhere I'm sure its simple but its got me stumped!

I've got a table structured like this (sorry I will learn proper way to post tables soon but it is late):
A----------B----------C
Adam---10/11/05---12
Adam---11/11/05---6
Adam---12/11/05---4
Adam---13/11/05---3
John----10/11/05---67
John----12/11/05---4
John----13/11/05---3
Jack----10/11/05---9
Jack----11/11/05---1
Jack----12/11/05---2
Jack----13/11/05---45

What I want is to have a sheet for each of Adam, John and Jack that shows there data. I have cell A1 that holds there name and the A3 onwards has the date and I want B3 onwards to hold the numbers from column C above that relate to the correct day and person.

This is a simplified version of what I want but if I can get this working I think I can get what I want to work.

Sorry if this is really simple but please post an answer it would really help me!

Oh by the way the dates are not always continous as you can see from john if he didnt sell anything one day then nothing appears. Also the sheets have all days down the left hand side and all sheets show the same dates ie 01/11/05-30/11/05.

Many thanks in advance

See,

http://www.mrexcel.com/board2/viewtopic.php?t=179640&start=10
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top