excel formula help 'if this name and this event next to that name look at the date ....

[drwafk]

Hello, I have a database with a column of names and next to the names an event name and next to that a date. For example:

Sheet1
A B C
1 Smith Tng1 6Oct16
2 Smith Tng2 30Sept16
3 Smith Tng3 8Jul16
4 Lopez Tng1 3Jun16
5 Lopez Tng2 4May16
6 Lopez Tng3 6Oct16
7 Lopez Tng4 24Sept

Sheet2
A B C D
1 Tng1 Tng2 Tng3
2 Smith X
3 Lopez

This will be cut and pasted into sheet 1 of excel from another database. On sheet 2 of excel I need a formula to look at sheet 1 and find a specific name first (in this case we'll use Smith) then when that name is found to look for the tng2 event then once its found the name and event to look at the date accomplished and if its older than todays date to populate an X into the cell on sheet 2. THe reason for this is because the data base will be random at listing the names. The names on sheet 2 will be constant. Just need to cell to sniff out the person, the event, and based on the date populate an x or not. I've simplified the example as the list I'm actually working with is ginormous. Thank you for your help.

[JohnTopley]

Please post a sample Excel file (not image) as I suspect your sample oversimplifies the data: what about duplicate names?
And is the last date a typo ..no year?



To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[drwafk]

Thanks. File uploaded.

[JohnTopley]

In B2 copy across and down

=IF(INDEX(Sheet1!$C$2:$C$9,MATCH($A2,Sheet1!$A$2:$A$9,0)+MATCH(B$1,Sheet1!$B$2:$B$9,0)-1) < TODAY(),"x","")

Assumes only one entry per name/event combination.

If there are duplicate names this will not work.

[drwafk]

In B2 copy across and down

=IF(INDEX(Sheet1!$C$2:$C$9,MATCH($A2,Sheet1!$A$2:$A$9,0)+MATCH(B$1,Sheet1!$B$2:$B$9,0)-1) < TODAY(),"x","")

Assumes only one entry per name/event combination.

If there are duplicate names this will not work.

Thank you but i need it to first look for a specific name in column A (A2:A100) then find the specific event in column b (B2:B100) and then in that row, find the date and determine if it's older than today.

[JohnTopley]

See attached: Sheet2

[drwafk]

Thank you so much for this formula. It got me going in the right direction however if I used a wider range in the columns the X would not populate properly. It wont look at the column of names if I use the B:B command for example. Am I forced to define the set rows as C2:C9, A2:A9 and B2:B9? I want it to look at the entire column and tried doing C:C, A:A, B:B. I also tried doing C2:C4200, B2:B4200, A2:A4200. I have 4200 entries to manage and I need this formula to work otherwise its a lot of data entry.

From here open the excel and read the text box, then continue with the below. I tried to put the formula here but it wouldnt let me.

What are the dollar signs for? I tried both above with manually entered dollar signs like you have provided me also.

In the formula you provided me why is there a $ before the A3 but not before the B1. On the B1 its between the B and the 1... B$!.

Your formula works good, I just want to expand the search area.

Thanks again.

[JohnTopley]

Try

=IF(INDEX(Sheet1!$C:$C,MATCH($A2,Sheet1!$A:$A,0)+MATCH(B$1,Sheet1!$B:$B,0)-2) < TODAY(),"x","")

This assumes heading(s) in row 1 and data starting in row 2

Or

=IF(INDEX(Sheet1!$C$2:$C$1000,MATCH($A2,Sheet1!$A$2:$A$1000,0)+MATCH(B$1,Sheet1!$B$2:$B$1000,0)-1) < TODAY(),"x","")

Change 1000 to your anticipated maximum number of rows


The $ signs are for "absolute" cell references so

$A2 will always refere to column A as you drag the formula ACROSS columns

B$1 will always refer to row 1 as you drag DOWN rows

$A, $B and $C fix these columns

[drwafk]

Try

=IF(INDEX(Sheet1!$C:$C,MATCH($A2,Sheet1!$A:$A,0)+MATCH(B$1,Sheet1!$B:$B,0)-2) < TODAY(),"x","")

This assumes heading(s) in row 1 and data starting in row 2

Or

=IF(INDEX(Sheet1!$C$2:$C$1000,MATCH($A2,Sheet1!$A$2:$A$1000,0)+MATCH(B$1,Sheet1!$B$2:$B$1000,0)-1) < TODAY(),"x","")

Change 1000 to your anticipated maximum number of rows


The $ signs are for "absolute" cell references so

$A2 will always refere to column A as you drag the formula ACROSS columns

B$1 will always refer to row 1 as you drag DOWN rows

$A, $B and $C fix these columns

Thank you, tried both suggestions and the x won't populate if older than today.

[drwafk]

Thank you for the explanation. I tried that formula and i think it does not like the *2*.
​When I paste the formula above it gives me this error:
"We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it, or click Help for more info on a common formula problems. Not trying to type a formula....."

I clicked on the insert function to look at the arguments and I think it's the *2*making it invalid.

[drwafk]

When I use the -1 in the INDEX formula it populates an X like I want but if I change the date greater than the current date the X stays there whereas it should go blank.

The -2 and the *2* populates a blank cell regardless of date.

I feel like we are really close to sealing this deal.

[JohnTopley]

If I change Tng2 for Smith to 30/10/2016 (from 30/09/2016) the "x" disappears (as it should) so I don't know why you are having problems.

This works whichever of the two formulas I use.

[drwafk]

I'll report back here as I left the spreadsheet at work for the weekend. I dont know what I'm doing wrong but I'm using the exact formula and its not working. I will say this...when I specify the columns to search ie.. the name and event (B59:B137) it works. My huge list of names happens to have one particular name (Smith in this case) from B59:B137. It would far too difficult to make a different formula for each and every name (there are hundreds). I want the names and events to be searched for by the B:B command.

Perhaps the date is in the wrong format....I'll report back here. THanks again, your help is priceless.

[JohnTopley]

Are all names and tags in a consistent form i.e. each same has the same number of tags as per your sample. If not, then try the following:

=IFERROR(IF(INDEX(Sheet1!$C$2:$C$5000,MATCH(1,($A2=Sheet1!$A$2:$A$5000)*(B$1=Sheet1!$B$2:$B$5000),0)) < TODAY(),"x",""),"")

Enter by holding down Ctrl and Shift together and then hit Enter: if this done correctly then you will see { ....} brackets appear round the formula.

Drag the formula down: you will get blank entries when there are no matches or entries in column A.

Use defined ranges rather than whole columns so change the 5000 to an appropriate value.

See the attached where "tng2" has been removed for "Smith"

[drwafk]

Are all names and tags in a consistent form i.e. each same has the same number of tags as per your sample. If not, then try the following:

=IFERROR(IF(INDEX(Sheet1!$C$2:$C$5000,MATCH(1,($A2=Sheet1!$A$2:$A$5000)*(B$1=Sheet1!$B$2:$B$5000),0)) < TODAY(),"x",""),"")

Enter by holding down Ctrl and Shift together and then hit Enter: if this done correctly then you will see { ....} brackets appear round the formula.



Drag the formula down: you will get blank entries when there are no matches or entries in column A.

Use defined ranges rather than whole columns so change the 5000 to an appropriate value.

See the attached where "tng2" has been removed for "Smith"

THIS WORKED! THank you. Only problem I have now is, if there is no date at all (meaning the person hasnt accomplished the event) I need it to populate an X versus being blank. How do I do that?

[JohnTopley]

Duplicate post

[drwafk]

I'll swing by work today and try this out. To answer your question no, not all names are consistent nor do they have the same number of events/tags. One guy will have 30 events and another guy will have 42 events and another 28 events, etc...

[JohnTopley]

So you want an "x" where event has been accomplished OR not accomplished ?????

[drwafk]

If it is past the current date then I want to show an X. This will trigger the column at the very end flagging that person for having a training item deficient. If his reoccurring date shows later (in the future)than the current date I want the column blank. Now if we have someone who is new or they havent initially accomplished the task it'll show a blank or no date (meaning never accomplished even once), I need it to populate an X so we can ensure he/she gets initial training. Sorry if thats confusing, thats the database I have to work with.

[JohnTopley]

It is confusing because using the same "code" (x) how do you which of the 2 conditions apply?

Can you post a sample file please.

[drwafk]

Basically i need the cell to see an older than the current date or no date to flag an X. Thanks again. Send me your paypal I'd like to thank you properly.

[JohnTopley]

To color your cell:

Select F2 (or whatever range)

Conditional Formatting:

New Rule

Use a formula ....

=COUNTIF($B2:$E2,"x") .... Change range to as many columns e.g B2:zz2

Format==>Fill==> Red

OK

Applies to: select column range e.g F2:f4 in your sample

For the a new name: the formula given will work as a "blank" date is treated as <= TODAY()

re PayPal: All time is given free but the offer is appreciated.

[drwafk]

To color your cell:

Select F2 (or whatever range)

Conditional Formatting:

New Rule

Use a formula ....

=COUNTIF($B2:$E2,"x") .... Change range to as many columns e.g B2:zz2

Format==>Fill==> Red

OK

Applies to: select column range e.g F2:f4 in your sample

For the a new name: the formula given will work as a "blank" date is treated as <= TODAY()

re PayPal: All time is given free but the offer is appreciated.

The COUNTIF formula works great, thank you.

I'm sorry, I was wrong, I need a blank date to return a blank cell. There are non-reoccuring items (Local orientation) that will not populate a due date and will be blank. The training item is accomplished and should not be shown as deficient. Is there a way to make no date/blank cell count as a blank cell?

[JohnTopley]

Try ..

=IFERROR(IF(INDEX(Sheet1!$C$2:$C$5000,MATCH(1,($A2=Sheet1!$A$2:$A$5000)*(B$1=Sheet1!$B$2:$B$5000)*(Sheet1!$C$2:$C$5000<>""),0)) < TODAY(),"x",""),"")

[drwafk]

It makes all the cells blank.

[JohnTopley]

Remember: Enter with Ctrl+Shift+Enter (as before).

[drwafk]

Remember: Enter with Ctrl+Shift+Enter (as before).

Genius. You are correct, thanks that worked like a charm. Think I'm all set now...I cannot thank you enough.

[drwafk]

My spreadsheet has so many formulas that its bogging down pretty good. Does this sound normal? Anytime I make an entry into any cell it takes literally minutes for it to be responsive again. On the bottom right hand corner it says 4 processes calculating with a count up to 100%.