#1
|
|||
|
|||
Index Match with multiple conditions
Hi everyone,
Could you help me with attached spreadsheet? On the left there is table with employees and leave they have approved. On the right, I need to make a visual representation of it. I would like for the code from Column F to appear in Cell J2 if Employee with EID 1 (search must be by EID) was on leave 11 Dec 23. I think this can be done with Index Match but I'm not able to do it myself. I have manually entered first line for as an example of outocme. Thank you for your time |
#2
|
|||
|
|||
Something like this?
I added a column for numbering rows into source table, and removed spaces from 2 column names there. And placed the result table on separate sheet. On sheet with result table, I moved dates to Named Range at top of sheet, and added non-formula headers to result table, which allowed the result table to be defined as Table too. |
#3
|
||||
|
||||
A different approach.
See table at cell H26. I changed a date (cell E3) in the source data to cause overlaps (I know, it may never happen) so you can see what happens in the results (T28:V28). If the source data changes, the table (a pivot table) will need refreshing (right-click it and choose Refresh). [The whole thing is a Power Query on your source data, which loads to the Pivot Table, with the data added to the Data Model so that a measure (txt) can be added and used in the Values area of the Pivot table.] Last edited by p45cal; 12-11-2023 at 07:02 PM. Reason: corrected name of attached file |
#4
|
|||
|
|||
@ArviLaanemets Do you know why the result table looses all the returns when I make any changes to LeaveRep table? When I update EID or try to add more lines, all results are disappearing. I can't even get them back by reversing my actions. Need to close and re-open workbook.
|
#5
|
|||
|
|||
@p45cal Pivot table is not refreshing with new information when I update source data. Additionally I can't add more lines to the source data. DO you know how I can fix this?
|
#6
|
||||
|
||||
On examining your file more closely, it looks as though you might be using Excel for Mac; can you confirm which version of Excel you're using?
|
#7
|
|||
|
|||
I need to be able to run this report on my work computer, not Mac. We use Excel 2016.
|
#8
|
|||
|
|||
Quote:
Only thing I did find that must be changed is for case the same date has response for more than 1 row (leave entries are overlapping). When this happens, either an error is returned, or in worst case the code from wrong row on LeaveRep sheet is returned. So the formula for all date columns on Report sheet must be like: Code:
=IF(COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1;"overlapping"; IF(SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0;""; INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))))) NB! As this site interpreted field name Code put in brackets as the code tag in posting, I had to replace the Code with C_ode. Before using the formula, correct this! The formula is somewhat long, so in case you want to use it, define some Dynamic Names (activate the top datarange cell in Date01 column of Report Table before defining them) and use them there. Like Code:
nOverLap = COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1 nEmpty = SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0 nRepCode = INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))) And the formula will then be like: =IF(nOverlap;"overlapping",IF(nEmpty;"";nRepCode)) |
#9
|
|||
|
|||
Quote:
I see that I can make changes to the report when working on Mac but results are disappearing on Windows PC - not sure what is happening there but I can do this on my personal computer. Although I really like the idea of report telling me when someone has couple of different leaves approved over the same period, I couldn't make the formula work for me. Made all the changes (code and commas) but still excel is asking me whether I am trying to write formula. My brain is definitely built-in with anty-excel functions Yes, originally I'm form Poland |
#10
|
|||
|
|||
Can't help with MAC - haven't never used it. What character coding is MAC using - ASCII or something other? I'm asking, because you mentioned Excel asking about you wanting to enter formula. Suspiciously like it doesn't recognize '='!
You use both MAC and Windows computers? Have you tried do all (started from entering formulas) using Windows computer only? |
#11
|
||||
|
||||
Quote:
1. As I mentioned before: "If the source data changes, the table (a pivot table) will need refreshing (right-click it and choose Refresh).". This is not automatic (but it can be made to be). 2. If you're using Excel 2016 for Windows my attachment to post #3 should work. Adding rows to the source data should be as easy as writing new entries immediately below the existing table, or pasting data to the same place. Just make sure the table extents cover your data (a small symbol in the bottom right corner of the bottom right cell of the table is apparent and you can click and drag it to cover your data, but this shouldn't be necessary since the table should adjust its size automatically. I know nothing about the Mac versions of Excel but I suspect they don't have the Data Model. So I'll wait until you try it at work. Try the file I attached directly at work, don't use one you've first opened and saved on your Mac. |
#12
|
|||
|
|||
Quote:
Just about the information disappearing from the table - the formulas are still there, just results are hidden. I know they are there as they flash momentarily when the new data is entered. |
#13
|
|||
|
|||
Quote:
I tried to create new spreadsheet and create pivot table coping what you have done. Could you let me know how did you create Data label to include all dates and txt EID (fx)? |
#14
|
|||
|
|||
Quote:
Btw. I added here an example, how make it even more 'background' |
#15
|
||||
|
||||
Quote:
Perhaps download the file I attached in msg#3 to your computer at home but do not open it. Email yourself to your work email and attach that file. At work, open the file in Excel. Otherwise, copy the file to a memory stick and take it to work. There are no macros in the workbook. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple match values for INDEX(MATCH) formula | jn82740 | Excel | 1 | 06-24-2023 11:48 PM |
Excel Index and Match function with multiple criteria not searching the next record | mushtaqkadar | Excel | 3 | 06-17-2017 12:20 AM |
UDF multiple IFs INDEX-MATCH | grexcelman | Excel Programming | 0 | 02-22-2015 04:20 PM |
Help with multiple match and index formula | ryanwood | Excel | 1 | 09-12-2012 07:53 AM |
Vlookup or Index/Match - Multiple Criteria | ruci1225 | Excel | 1 | 01-15-2012 07:31 AM |