VLOOKUP Multiple Lines

[jonnygilescronin]

Hi, i am working on a project & I am having difficulty with extracting multiple data from one worksheet to another, Basically, if the Cell "C2" is equal to lets say "Tesco", I am looking for all the Tesco Data from the download data to show in the Template Worksheet, & then similarly if "c2" is pressed for "Sainsburys", i want the sainsburys data to show in the template.
Is this possible?

What I have at the minute is being only able to get 1 line of data out, but will have much more than that & different qtys of lines for each "Supplier"

I have attached a data file to easier understand what I mean

Cheers
Jonny

[JBeaucaire]

This is how I would do it, VLOOKUP isn't suited for this. I've addded a key column to the data to help provide an INDEX to match rows to, then used a ROWS() function to list the numerical matches. It works, and no array formulas.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[jonnygilescronin]

Thanks
I cant add any further data, basically the downloaded info is the only info I can work with.
The project is to automate the dowloaded info to the template worksheet & the formulas calculate the rest.

Thanks
Jonny

[martindwilson]

well you could use an array but from experience with a load of dat it slows right down. best bet is to use a macro. which simply auto filters data on whatever criteria you want then pastes it onto the summary sheet

[jonnygilescronin]

Thanks Martin,
I have already macro'ed the downloaded data to seperate "suppliers".

I will keep trying as in the real project there are 100's of suppliers & the file needs to be updated monthly.

I hope to get away from copy & paste & just download the info monthly (10 minutes), macro it (5 minutes)& then press c2 for the supplier i want to see(priceless).

Copy & paste is using severe resources as you now may understand.

Regards
John

[martindwilson]

not sure about that! auto filter is quick and the copy paste takes seconds!
far far quicker than using array formula.

[JBeaucaire]

What do you mean you can't? That makes no sense at all. Once you have the data, you can do what you need to do to get your report. That's the point of Excel, do what you need.

Is this some sort of homework assignment with abnormal restrictions on it?

[jonnygilescronin]

Thanks for your help Mr Beaucaire
I am afraid I have been thrown in at the deep end but your formulas are helping so well & I have learnt so much more from Excel by studying them.

I am having some difficulty to solve 1 issue where if i have the same part number in 2 columns with 2 different start dates, then the formula will only show the first date recognised.

I have attached the example & changed a column in order to replicate the problem.

Again, many thanks for your expertise & aiding in my understanding of what excel is really capable of.

John

[JBeaucaire]

I always solve situations of repitition by adding a column to the original data that creates a TRULY unique value for each row. Before I spend any time designing one for you, is that OK to do? You seemed hesitant before about changing things.

I'm talking about adding 1 column to the DOWNLOAD data. I realize this data is downloaded, but you can add the formula I have in one cell in the new column and just copy it down, 5 seconds work.

Let me know.

[jonnygilescronin]

Hi Mr Beaucaire
Thanks alot, I cleared up a few issues with regards change & if changes are necessary then thats it.

Please go ahead with the formula, I can apply it to my Project & see hows things go.

Regards
John

[JBeaucaire]

OOPS! My bad...we'd already added a FILTER KEY on the data sheet. So, I just should've kept drawing values based on matching the KEY column. Bad design on my part.

I've corrected the formulas in the chart so they all use the Filter Key. Sorry about that.

[martindwilson]

i gave up and just added the array

[JBeaucaire]

i gave up and just added the array

LOL...hehe, didn't see the filter key in column K, did you? (wink) Array isn't needed once I used the key correctly.

[jonnygilescronin]

Thanks Guys, You been marvelous.

I am stuck on 1 final issue which is really bugging me, Basically, I have a list of dates of which I received defects. the full qty of defects per month needs to entered on a different Cell, please see attachment.

I am trying to place the sum of all the defects from a month into the cell.

Thanks Guys
John

[martindwilson]

=SUMPRODUCT((MONTH(A2:A100)=1)*(B2:B100))
will give january

[JBeaucaire]

If you want a single formula that works all the way down, put this in F14 and copy down:

=SUMPRODUCT(--(MONTH(A2:A100) = MONTH(DATEVALUE(LEFT(E14, 3) & 2009))), B2:B100)

[jonnygilescronin]

Thanks Guys,
I have a formula already in Column B which is extracting from another previous worksheet, I think this is what is effecting my output,I am getting #VALUE! in the F Column where I put the new formula.

Do you have any ideas?

Regards
John

[martindwilson]

post your sheet again please jgc

[jonnygilescronin]

Hi Martin
Attached the sheet, no formula in this sheet but in the original project I am working on.

Thanks
John

[martindwilson]

cant see a problem totals in f look fine
perhaps tour dates ar somehow different need top see some examples

[jonnygilescronin]

Hi Martin
Everything in the example I posted is ok, the problem is my original project.

The qty in Column B is calculated using a formula in the original & when I use the formula example you gave me on the original, due to this previous formula which gets my data from another worksheet, I believe that is why I am getting the error #Value!

How do I extract a value & not let the formula interfere from column B, I guess if what I am trying to say.

John

[martindwilson]

cant tell without seeing ,column b values should still be totaled with that function.
make sure the range doesnt include header row
in example
=SUMPRODUCT((MONTH(A1:A100)=1)*(B1:B100)) wont work but
=SUMPRODUCT((MONTH(A2:A100)=1)*(B2:B100)) will
its because =MONTH(A1) returns an error hence the #value!
this would also apply iff you have non dates anywhere in range

but
=SUMPRODUCT(IF(ISNUMBER($A$1:$A$100),MONTH($A$1:$A$100)=1)*$B$1:$B$100) array entered with ctrl+shift+enter should ignore non dates/numbers
{=SUMPRODUCT(IF(ISNUMBER($A$1:$A$100),MONTH($A$1:$A$100)=1)*$B$1:$B$100)}

[jonnygilescronin]

Hi
I have attached a butchered file from my project to show you what i mean.

It seems when the formulas are in vacant cells C60 to C95 it effects the value in E100. Take out the formulas & the result appears.

I appreciate your time & help on this.

Thanks
John

[martindwilson]

with a lot of help from donkeyote showing stupid me in the right answer replace formula in e100
with
=SUMPRODUCT(--(TEXT($C$55:$C$68,"mm")="01"),$F$55:$F$68)

[jonnygilescronin]

Hi All
I am looking for 1 final piece to complete my mass jigsaw of a excel sheet.

I want to apply the usage as per the month.

If I have a usage for all 6 months I can do it easily, but when i have no data for some months(Jan) & data for certain months(March), my formula places all the data starting from March Data into the January cell.

I have attached the file if somebody can help.

Again Many Thanks, I wish I could show you the project as many people have been quite impressed.

Regards
John

[JBeaucaire]

Change the formula in C10 to:
=IF($B10="", 0, INDEX('Download Data'!C:C, MATCH(ROWS(B$10:B10), 'Download Data'!$K:$K, 0)))
...and copy it down.

Then put this in B106 and copy down:
=SUMPRODUCT(--(MONTH(DATEVALUE($A106&$C$101)) = MONTH($C$10:$C$101)), $F$10:$F$101)

[jonnygilescronin]

Thanks, but I may not have explained myself correctly.

The information I want is taken from the usage work sheet & not inside the Template & Download data worksheets.

I will also check this formula as it may help me in the future.

Thanks
John

[JBeaucaire]

Instead of explaining, show us your expected results and highlight where you got it from.

[jonnygilescronin]

Hello Mr Beaucaire
Sorry i have not replied as I have been out of the office on sick leave.

I have attached the updated worksheet as you requested.

When I have a usage for each month (TESCO Cell C2)my formula in B106 - B117 works no problem,

My problem is when I do not have a usage for each month
i.e. Sainsburys(Cell C2) Usage March May & June only, The data for March, May & June is taken & placed in the 1st cell B106 for January, B107 Feb & sso on, when I require it to go to B108, B110 & B111 respectively.

Thanks
John

[JBeaucaire]

On the Usage page, change the "key" formula in D2 to:
=A2&"-"&MONTH(B2)
...and copy down.

On the template, change the formula in B106 to:
=IF(ISNUMBER(MATCH($C$2&"-"&MONTH($A106), Usage!$D:$D, 0)), INDEX(Usage!$C:$C,MATCH($C$2&"-"&MONTH($A106), Usage!$D:$D, 0)), "")
...and copy down.

On Template, you'll need to replace the TEXT strings in A106:A117 with actual date strings, you can format the cells as mmm to still only show the month abbreviation, if you wish.

On Usage, you'll need to change the text strings in B2:B11 with actual date strings, you can format the cells as m.yyyy to still show the dates in the same format, if yo wish.

[jonnygilescronin]

Hi Mr Beaucaire
Thanks for the formulas

I am having some difficulty with formating the cells on the usage worksheet to m.yyyy

I believe it is because of the dot that is being downloaded from the main server data -i.e. 1.2009

Is there a way to format the whole column to show Jan-09, Feb-09 etc. if the data is 1.2009 2.2009 without having to scroll down after formatting 1 cell.

I have again attached the excel sheet with the data on the usage worksheet.

Regards
John

[JBeaucaire]

Your sample file shows 1:2009 but your original data was 1.2009 and neither one of those is a standard date format, so you'll need to convert it to a real date Excel can use.

For instance, in I2, enter this formula and copy down:
=DATE(RIGHT(H2,4),LEFT(H2,FIND(":",H2)-1),1)

Then format that column as mmm-yy to get the display you want. You can copy/edit/pastespecial/value that new column back over the original data to make the new data permanent.