Lookup with Repeating Values

**pauldaddyadams** · 10-29-2009, 04:13 PM

Hi,

I wonder if anyone can help, I have tried searching for a solution but I have had no luck.

I have a spreadsheet in which data is dumped from a database, there is a dump (on a new tab) for each site.

I need a summary sheet which will add up the two tabs. My problem is however is that I can not use Vlookup as the same codes are used and it will only return the first occurrence.

e.g.
444588 Prime Wages/salaries
This code appears under Production prime labour and Distribution Labour but will only ever return the production prime labour value

Another example would be
444586 Ohd Wages & salaries
This code appears under Production Overhead, Site Overhead, Sales overhead and Admin Overhead this would only ever return the production overhead value only

Is there any way that I can get the summary sheet to use a formula where it looks up the department and then the code? I was thinking index and match but I was unsure if this was possible also.

Worth noting is that the summary sheet has a list of all codes that could be used but the dumps will only ever have values if a cost has been occurred, there for the number of rows in each department is not consistent. There will be a number of tabs however in the attached example I have only provided two.

Any help would be great, please ask if something is unclear

**Special-K** · 10-29-2009, 05:26 PM

MATCHing on ("Production Prime Labour" ) - 1 should indicate the last row in that group
use that to specify your VLOOKUP range.
Second VLOOKUP range would be MATCHing on ("Production Prime Labour") +1 and a MATCH on ("Distribution Labour") - 1

Probably a mixture of VLOOKUP MATCH and ROW

**pauldaddyadams** · 10-29-2009, 05:33 PM

Thanks for the reply - can you help with the formula at all? I am unsure how to write it as I dont fully understand the solution, I am still learning index and match.

**pauldaddyadams** · 10-30-2009, 05:21 AM

The codes below will appear on every sheet in the workbook. So can a formula be set up where it would match the text below and then it looks at the data above it until it finds the next match?

* Production Prime Labour
* Distribution Labour
*** Total Production Overhead
*** Total Site Overheads
*** Total Sales Overhead
*** Total Administration Overhead

**DonkeyOte** · 10-30-2009, 05:32 AM

You can not easily conduct conditional summation in 3D ... to do so using native functions alone would necessitate expensive & volatile formulae.

For an overview of the options available to you see John McGimpsey's page on the subject: http://www.mcgimpsey.com/excel/threedsumif.html

**pauldaddyadams** · 10-30-2009, 07:28 AM

Hi

I couldnt get the morefunc.xll add-in download - I believe the link is down. Is this the function I should be looking at?

I am thinking that the SumIF forumlas on the spreadsheet would not work, is this correct? The Cell ranges are not consistant on each sheet.

**DonkeyOte** · 10-30-2009, 07:32 AM

I believe the CNET link to morefunc still works:

http://download.cnet.com/Morefunc/30...-10423159.html

Re: SUMIF - correct, yes, I didn't look at your file in detail I'm afraid - I should have. Unfortunately at the moment I don't have any time to look at this but I will look in later and offer what little advice I can should you still need it.

**pauldaddyadams** · 10-30-2009, 09:24 AM

Sorry to have to ask DonkeyOte but I tried the threed funtion on the add in and I had no luck.

When you get the chance can you have a little look? I would really aprechiate any advice.

**DonkeyOte** · 10-30-2009, 11:56 AM

Attached is an example utilising the volatile SUMPRODUCT approach (as illustrated on the McGimpsey page).

The present arrangement is setup such that it assumes the month columns on each summation sheet are the same (ie months appear in same position) - if this is not the case the SUMPRODUCT would require further modification which would in truth make it (even) less efficient.
Alter the ranges in terms of rows used to suit however always try to keep as lean as possible when dealing with SUMPRODUCT / CSE Arrays - as is performance is likely to be poor but with large ranges things will probably grind to a halt.
The list of sheets to be summed should be in Z1 onwards and no blanks should be interspersed amongst the sheets (ie consecutive listing) - ensure all sheets listed exist!

In reality if possible it's best to condense the source data into one sheet for subsequent analysis (ie amalgamate dump1 & dump2 and analyse the amalgamation).

**pauldaddyadams** · 10-30-2009, 12:54 PM

With the example you posted I still have the problem that it will only pick up the first occurrence of the code. For instance 444588 appears under production labour and distribution labour.

I need something which will first identify the department and then maybe index and match the codes e.g. 444588 depending on what department it is in.

Can the array be defined so that it looks for the key words that will feature on every worksheet (* Production Prime Labour, * Distribution Labour, *** Total Production Overhead, *** Total Site Overheads, *** Total Sales Overhead, *** Total Administration Overhead) and everything between each key word will become the lookup table.

Can anything be done? I have spent ages looking around the net but couldn’t find a solution, I was surprised no one else has had the problem – the dumps are directly from SAP which I then want to use in an excel summary page.

**DonkeyOte** · 10-31-2009, 05:42 PM

OK I missed that point !

However you opt to do this you should I believe create a key on each tab (incl. summary) -- on the source sheets it is imperative that the "key" should appear in a common column (ie the same column on each sheet... summary sheet need not use the same)

In the attached I am merely given proof of concept - I am not saying "these are the columns you must use" etc but realistically you want to use a Key to ease the burden as best you can.

**pauldaddyadams** · 11-01-2009, 03:26 PM

Adding a key like this wouldnt be a problem, as you have forumlated the key it should be easy to do.

What does this mean:

Please Login or Register  to view this content.

How is it finding the department it is in, what does this mean ("~**")

**DonkeyOte** · 11-01-2009, 04:16 PM

* in a MATCH is by default a wildcard character...

Consider

A1: I like to buy cars I really do
A2: I like *cars*!
A3: I like cars

Let's say I want to find the first instance in which the phrase "cars" appears... I can't use:

Please Login or Register  to view this content.

all of the time because on occasion this phrase may be embedded within the strings...
in the MATCH function we can however make use of wildcards, and in particular the * (ie anything)

Please Login or Register  to view this content.

The above will return 1 as the phrase can be found embedded within the string in A1.
Note the use of * phrase * ... this equates to:

anythingcarsanything

If I were to change the function and remove the 2nd asterisk

Please Login or Register  to view this content.

my criteria is now in effect

anythingcars

This will now return 3 as only A3 contains the string "cars" followed by nothing (both A1 & A2 have things appearing post "cars" and are thus excluded).

Now getting on (finally) to your point... given we now know the role of * as a wildcard how can we search for an asterisk literally ? ie how can I find the literal phrase *cars* (A2) ...
To use an asterisk literally we precede with tilde (~), eg:

Please Login or Register  to view this content.

so our criteria now says

anything*cars*anything

ie the first and last asterisks used act as standard wildcards (not preceded by tilde) however the 2nd and 3rd instances are preceded with tilde meaning we're looking for them as literal characters.

So in the formula:

Please Login or Register  to view this content.

We're trying to find the first string which begins with an asterisk literally, ie

*anything

This will find for us the department footer which we can append to the primary code thereby giving us a unique codedept identifier.

**pauldaddyadams** · 11-01-2009, 04:39 PM

Thanks DonkeyOte - The time you devote to everyone on this forum is greatly appreciated I am sure.

I will look through your explaniation in detail in the hope to learn a few new things.

Ill be creating the spreadsheet at the end of the week - without your help these last few days I wouldnt have been able to!!!

**pauldaddyadams** · 11-03-2009, 02:40 PM

Hi,

Can someone help with the formula please?

I have the formula:

Please Login or Register  to view this content.

Which looks in all work sheets in a list however I need it changing slightly so it only looks in one spreadsheet (dump 1240)

I have provided an example spreadsheet. On tab “1129” I would need it to index and match the information on the tab “dump 1129”

Can anyone help? Most of the formula will stay the same but the place where it looks up will change.

**DonkeyOte** · 11-03-2009, 02:42 PM

New question - new thread please.

Lookup with Repeating Values

LinkBack

Thread Tools

Rate This Thread

Display

Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Re: Lookup with Repeating Values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions