PDA

View Full Version : How to match index with two criterias and multiple dates?



kiisaaa
11-22-2016, 03:30 AM
Hi,

I am really struggeling with getting this spreadsheet to work.

I have one 'mother table' of data, containing information on all of our sales including name of the buyer and the date he bought, and several smaller lists of names targeted for our marketing campaign which contains the name of the company to the buyer, but not without the specific dates. I have tried several index match formulas to pull the dates from the mother sheet to the marketing sheet.

I do know the dates and the lenght of the marketing campaigns, but not when/if the customer bought any products.

My goal is to get the closest date, matching the name from the mother sheet and a target date range returned into the smaller lists, but i can't get the formula to work :(:(

Issue 1: Not all/few names in the sample sheet are for real in the mother sheet
Issue 2: We only want to return the date if it is between two target dates
Issue 3: I want to be able to add several small sheets, doing the same procedure

kiisaaa
11-24-2016, 12:49 AM
No one ?

KevO
11-25-2016, 10:26 AM
Your workbook contains no data (except two lists of names and one list of names and dates). I cannot work out what (other than the name) should match.
Both lists contain duplicate entries for names and therefore index & match or vlookup will not work without a helper column - both functions find and use the first matched item and will ignore anything else.

Please attach an amended worksheet with some data for a FIVE names in both files - with several rows for each name in both files
include:
1. the marketing sheet before matching,
2. the mother sheet,
3. a third sheet showing how you want to see the end result after matching with explanation how a match is made

NOTE - make anything confidential anonymous but it must look like the original data in format so that we can anticipate likely issues.
thanks
Kev

kiisaaa
11-29-2016, 01:11 AM
Thanks,

look at my new file, please.

The only problem is the inconsistency here.
I need to match them only if they appear in a given time interval and there might be several names in any of the sheets.

Mother sheet is usually at 4000 names,
the product sheet is usually at 500.

Thanks for helping out, Kev :)

KevO
11-29-2016, 08:57 AM
Thanks

Question1 Are you wanting to add the dates (from Mother sheet) to a marketing campaign list (containing a specific list of customer targeted in that campaign)?
(or only to the product list)

By filtering the data on "Mother Sheet" we get:

17721

Question2 Is this the results you expect?

kiisaaa
12-01-2016, 03:20 AM
1: Yes - I want to add the data, but not with filters.
The mother sheet is dynamic - we are extracting the names from a database.
The marketing campaign sheets are not dynamic.

I need the corresponding dates from the mothersheet into the sheet named "Product 2" because I will use that data later on to make some graphs and statistics on how well our marketing campaigns are working.


I expect to use an index match formula, matching the corresponding date from mothersheet into "product 2".
It should be able to pull the correpsonding dates IF the date in the mothersheet is inbetween a given time intervall, otherwise leave the cell blank.

I have seen some index match formulas combining the Small formula complete this task, but I can't get any of them to be applied to my sheet :s

KevO
12-01-2016, 04:12 AM
This is what I do not understand:

The Product list contains a customer name (say) 5 times (say rows 2,14,26,28,30)
The Master sheet contains a customer name (say) 3 times (because we are looking between 2 dates)

Against which rows in the Product list should the 3 dates appear?
-the first 3 (ie rows 2,14,26) ???

p45cal
12-01-2016, 06:40 AM
Would a table such as below be any use?


17740

kiisaaa
12-06-2016, 02:18 AM
This is what I do not understand:

The Product list contains a customer name (say) 5 times (say rows 2,14,26,28,30)
The Master sheet contains a customer name (say) 3 times (because we are looking between 2 dates)

Against which rows in the Product list should the 3 dates appear?
-the first 3 (ie rows 2,14,26) ???

The first ones, it does not really matter as long as it fills up the dates from the master sheet from inbetween the 2 dates. :-)

kiisaaa
12-06-2016, 03:07 AM
Would a table such as below be any use?


17740

Thanks for looking at my problem

I need to have the corresponding date matched (or atleast the most likely corresponding date), so I am not sure if that would be sufficient enough.
I do believe a match index formula with a time restriction could be usefull.

p45cal
12-06-2016, 08:52 AM
I need to have the corresponding date matched (or atleast the most likely corresponding date),It is.

17770
If it hadn't have been, it would have looked like:

17768





so I am not sure if that would be sufficient enough. Nor am I. You have blanked all other fields in the Mother sheet. In the following I have invented some Oprette data to make the two Thor entries non-identical, and you could have something like:
17769





I do believe a match index formula with a time restriction could be usefull.it may be… but very hard to make it give what I think you want.

kiisaaa
12-08-2016, 03:15 AM
Hmm, I could look at the Pivot solution, but I would rather prefer to have it in a formula.

17790
I found this online, but I can't find any good ways to adapt it to my sheet.
The formula is: =iferror(index($b$2:$b$17;small(if(frequency(if($a$2;$a$17>=$d$2;if($a$2:$a$17<=$e$2;match($b$2:$b$17;$b$2;$b$17;0)));row($b$2;$b$17)-row($b$1));row($b$2:$b$17)-row($b$1));counta(f$1:f1)));"")

I have several scenarios in my work where such a formula would be very usefull.