CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
D_M
D_M

Reputation: 134

Excel formula Comparing two arrays

I have a table (xTable, 3 columns:Name, Start, Stop). On another sheet I have a calendar I am trying to make. The A1 row are dates. The A1 column are names.

I am trying to compare the start date in my xTable to the date in my A1 row. If the date is found, look at first column of the xTable, if it equals the name of the person in A1 colum, return true, if not, return false.

Something like: =INDEX($B$2:$AF$14,ROW(A2)-1,MATCH($B$16,$B$1:$AF$1,0))="x"

But for the life of me, i cannot get it to work...I was thinking maybe because I have about 400 rows of data, but I just am not sure. Any help is appreciated! Also, if you know of a vba solution, I could always use that..but I prefer try to keep it in formulas. But beggars should not be choosy ;)

     | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |etc---> 
Name1|____|____|____|____|____|____|____|____|____|____|____|____|
Name2|____|____|____|____|____|____|____|____|____|____|____|____|
Name3|____|____|____|____|____|____|____|____|____|____|____|____|
Name4|____|____|____|____|____|____|____|____|____|____|____|____|
Name5|____|____|____|____|____|____|____|____|____|____|____|____|
Name6|____|____|____|____|____|____|____|____|____|____|____|____|
Name7|____|____|____|____|____|____|____|____|____|____|____|____|
Name8|____|____|____|____|____|____|____|____|____|____|____|____|
Name9|____|____|____|____|____|____|____|____|____|____|____|____|


Name |Start|Stop |
Name1| 3   |  6  |
Name1| 1   |  12 |
Name7| 10  |  10 |
Name3| 21  |  30 |
Name1| 9   |  12 |
Name9| 7   |  15 |
Name9| 1   |  3  |

Upvotes: 3

Views: 5133

Answers (1)

Jerry
Jerry

Reputation: 71598

If I understand right, you want to populate the calendar with the start dates from xTable with TRUE when found and with FALSE otherwise?

You can use this formula in cell B2 and fill it right and down:

=IF(COUNTIFS($A$13:$A$19,$A2,$B$13:$B$19,B$1*1)=0,FALSE,TRUE)

I'm using B$1*1 just in case you have the dates stored as number in row A1. Otherwise, if they are stored as date but formatted as dd (or equivalent), use DAY(B$1) instead.

The formula will count the number of occurrences of a row with the corresponding name and start date of table xTable using COUNTIFS. If none are found, it will return 0, which yields to FALSE.

Upvotes: 1

Related Questions