Multiple Matches but only pulling 1st one

C

CrashOD

Board Regular
Joined
Feb 5, 2019
Messages
118
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
tried doing this codes looking for
C12 ' is the value i'm wanting to search for were they can be multi matches if there is i need B44-B46, D44-D46 have values in there
'SK Real Estate'!$U:$U ' is the location to look for the info to match to (C12)

B44 ' should show SK Real Estate'!$H:$H
'SK Real Estate'!$H:$H ' (Date) is the value i want returned if there is a value

B45 ' should show 'SK Real Estate'!$J:$J
'SK Real Estate'!$J:$J ' (Date) is the value i want returned if there is a value

B46 ' should show 'SK Real Estate'!$L:$L
'SK Real Estate'!$L:$L ' (Date) is the value i want returned if there is a value

D44 ' should show SK Real Estate'!$I:$I
'SK Real Estate'!$I:$I ' (Amount) is the value i want returned if there is a value

D45 ' should show 'SK Real Estate'!$K:$K
'SK Real Estate'!$K:$K ' (Amount) is the value i want returned if there is a value

D46 ' should show 'SK Real Estate'!$M:$M
'SK Real Estate'!$M:$M ' (Amount) is the value i want returned if there is a value

I tried the below codes with no luck. B44 & D44 get filled in but nothing more. on the picture below with the black boxes line 1170 in $U matches for the 2nd time witch should fill in B45 & d45 for 2nd payment.

=IF(ISNUMBER(MATCH('SK Real Estate'!$A:$U, $C$12)), MATCH($C$12('SK Real Estate'!$A:$U), ROW('SK Real Estate'!$A:$U)), "")

=INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),8)

=IFERROR(INDEX('SK Real Estate'!$H:$H, SMALL(IF($C$12='SK Real Estate'!$U:$U, ROW('SK Real Estate'!$U:$U)-ROW('SK Real Estate'!$U)+1), ROW(1:1))),"" )


this picture shows were im pulling data from (this exact sheet in in the same workbook im looking to put code in. why i used 'SK Real Estate'! and not reference a workbook then sheet
excel.png



the picture below is were im doing the code for B44:B46 & D44:d46. yes the total due is wrong for face changing code to pull the other amount on sheet should be
321.41​
327.97​
360.77​

1654277725194.png


Thanks so much for any help!
 
Sort by date Sort by votes
Have you tried other numbers to see if they return correct results. And have you tried manually typing the number in question into C12?
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
i manule typed it same issue, if i do a diffrent number one with 3 payments i get the first value but other two dont fill in
 
Upvote 0
this is were the parcel is enter manually
Automated 2022 Tax Certification Form.xlsm
ABCDEFGHI
1CONTACTDAYS
2$UNPAID OR PAIDPARCEL #OWNERSADDRESSREQUESTED BY:PHONE, EMAILSent DateUNPAID
List

Automated 2022 Tax Certification Form.xlsm
ABCDEFGHI
162 013-02-005-317 
List
Cell Formulas
RangeFormula
A162A162=IF(B162="UNPAID",0,IF(B162="PAID",30,""))
I162I162=IF(A162=0, DAYS(TODAY(), H162), "")


this is the first spot it is called for
Automated 2022 Tax Certification Form.xlsm
AB
10Request For Updates Good TillFriday, 24. June 2022
11
12REQUESTING COMPANY: HOMETOWN ABSTRACT
13DATE REQUESTED: Friday, 10. June 2022
142yr Tax Cert AMOUNT DUE:$30.00 Tax Certification Fee Due Per Parcel
154yr Tax Cert AMOUNT DUE:$60.00 Tax Certification Fee Due Per Parcel
16
17PARCEL:013-02-005-317
Tax Cert Bill
Cell Formulas
RangeFormula
B10B10=(B13+14)
B12B12=LOOKUP(2,1/(List!F:F<>""),List!F:F)
B13B13=NOW()
B17B17=LOOKUP(2,1/(List!C:C<>""),List!C:C)
Press CTRL+SHIFT+ENTER to enter array formulas.


this is what we have been working on
Automated 2022 Tax Certification Form.xlsm
ABCDE
12Tax Parcel:013-02-005-317
Tax Cert Form 2022-2021
Cell Formulas
RangeFormula
C12C12='Tax Cert Bill'!B17

Automated 2022 Tax Certification Form.xlsm
ABCDE
42SCHOOL 2021July/AugSept/OctNov/Dec
43Discount (-2%)Face:Penalty (+10%)
44PAID$64.07$65.38$71.92
45Date Paid:9/15/21HomeSteadAssessed Value
46NO1,810
47PARTIALSDATE$65.38<- SHORT IN FACE
48PAYMENT #101/00/1900$0.00FACE
49PAYMENT #2   
50PAYMENT #3   
Tax Cert Form 2022-2021
Cell Formulas
RangeFormula
C44C44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),11)
D44D44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),12)
E44E44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),13)
B44B44=IF(B45="","UNPAID",IF(B45="EXONERATED","PER COUNTY",IF(B45="Tax Claims", "SHORT", "PAID")))
B45B45=IF(D47<=0,C50,IF(D44>D47,"Tax Claims",IF(INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)="","",INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1))))
E46E46=INDEX('SK Real Estate 2021'!$T$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)
D46D46=INDEX('SK Real Estate 2021'!$U$2:$AI$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),15)
D47D47=IF(D48="","",IF(D44<=SUM(D48,D49,D50),0,IF(SUM(D44,-N(D48),-N(D49),-N(D50))<0.02,0,IF(D44>SUM(D48,D49,D50),SUM(D44,-N(D48),-N(D49),-N(D50)),"help"))))
C48:C50C48=IFERROR(IF(ROWS($C$48:C48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($C$48:C48)-1),""),"")
D48:D50D48=IFERROR(IF(ROWS($D$48:D48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($D$48:D48)),""),"")
E48E48=IF(C48="","", "FACE")
E49:E50E49=IF(C49="","",IF($D$48>=D49,"FACE",IF($D$48<D49,"PENALTY")))
 
Upvote 0
Are you certain that as you look down column U, it is the first match that you want? The formula will use only that row. So if there are multiple rows where a match could occur, you will only get results from the first.
 
Upvote 0
u is the only place that has a parcel

Automated 2022 Tax Certification Form.xlsm
ABCDE
42SCHOOL 2021July/AugSept/OctNov/Dec
43Discount (-2%)Face:Penalty (+10%)
44SHORT$64.07$65.38$71.92
45Date Paid:Tax ClaimsHomeSteadAssessed Value
46NO1,810
47PARTIALSDATE$21.80<- SHORT IN FACE
48PAYMENT #110/29/2021$21.79FACE
49PAYMENT #209/15/2021$21.79FACE
50PAYMENT #3   
Tax Cert Form 2022-2021
Cell Formulas
RangeFormula
C44C44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),11)
D44D44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),12)
E44E44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),13)
B44B44=IF(B45="","UNPAID",IF(B45="EXONERATED","PER COUNTY",IF(B45="Tax Claims", "SHORT", "PAID")))
B45B45=IF(D47<=0,C50,IF(D44>D47,"Tax Claims",IF(INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)="","",INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1))))
E46E46=INDEX('SK Real Estate 2021'!$T$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)
D46D46=INDEX('SK Real Estate 2021'!$U$2:$AI$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),15)
D47D47=IF(D48="","",IF(D44<=SUM(D48,D49,D50),0,IF(SUM(D44,-N(D48),-N(D49),-N(D50))<0.02,0,IF(D44>SUM(D48,D49,D50),SUM(D44,-N(D48),-N(D49),-N(D50)),"help"))))
C48:C50C48=IFERROR(IF(ROWS($C$48:C48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($C$48:C48)-1),""),"")
D48:D50D48=IFERROR(IF(ROWS($D$48:D48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($D$48:D48)),""),"")
E48E48=IF(C48="","", "FACE")
E49:E50E49=IF(C49="","",IF($D$48>=D49,"FACE",IF($D$48<D49,"PENALTY")))


so now just not showing the 3rd payment. figures i pic a hard one. this person made the 2nd and 3rd payment before the first one. so i fixed that. now shows the first two now missing last one.
Automated 2022 Tax Certification Form.xlsm
HIJKLMNOPTU
25647/30/21$76.589/14/21$76.5710/18/21$76.57225.13$76.57252.698500012-02-004-053
256510/29/21$21.799/15/21$21.7910/18/21$21.7964.07$21.7971.921810013-02-005-317
25667/12/21$65.748/20/21$65.7410/18/21$65.74193.28$65.74216.947600015-02-007-067
SK Real Estate 2021

Automated 2022 Tax Certification Form.xlsm
HIJKLMNOPTU
243310/29/21$21.799/15/21$21.7964.0721.7971.921810013-02-005-317
SK Real Estate 2021

Automated 2022 Tax Certification Form.xlsm
HIJKLMNOPTU
27088/3/21$80.688/30/21$80.6610/29/21$80.6680.66266.206700016-01-008-014
27097/30/21$161.479/14/21$161.4710/29/21$161.45474.68$161.45532.8115550011-01-003-135
271010/29/21$21.799/15/21$21.7910/18/21$21.7964.07$21.7971.921810013-02-005-317
SK Real Estate 2021
 
Upvote 0
i tried a 2nd person with 3 payments shows the first 1 but not 2-3rd also an there in order and cells have no spaces.
 
Upvote 0
If you go way back and carefully read my post #2, you will see this:
KRice said:
This assumes the match for C12 will be found on only one row in column U of the SK Real Estate sheet. If there are multiple rows where C12 matches, then more needs to be known to determine which row is of interest.
Click to expand...
I was clear that the formula would use only the first match that it encountered in column U. I am not asking if column U is the column that has the parcel numbers (that seems to be the question you are answering). I am asking if the parcel number can be found on multiple rows as one looks down column U. The answer is yes. You finally show, after 44 posts in this thread, that multiple matches can occur and that when they do, they are not necessarily in the order required by the formula. In this case, we find parcel 013-02-005-317 on rows 2433, 2565, and 2710. Only the first match is considered, so you will get the results found on row 2433. This was explained in post #2. That row 2433 contains only two payments/dates...and that is what you got for your results.

The current formula is not compatible with your data. Your source data would need to be sorted to ensure that the first row of any parcel match is the one showing the most transactions...that is the only way the current formula will work reliably. I'll have to think a bit about another approach that wouldn't involve sorting the source data.
 
Upvote 0
yes, the first sheet i had i sorted i can sort the 2021 sheets and prior since there copies. but the 2022 i can not since they are required for reports untouced. till done with year end.
is there a way that i can have the workbook im useing that has the cert, 2021-2019 sheets. to pull all data to a sheet names SK Real Estate from when new book is recived in july. '[rel_school 2022.xls]Real; Estate'

so pull all data from
'[rel_school 2022.xls]Real; Estate'
to worksheet
SK Real Estate in Automated 2022 Tax Certification Form.xlsm

and sort it by date
RLE_School 2021.xls
ABCDEFGHIJKLMNOPTUVZADAEAFAGAHAIAJAKAL
1DistrictDateOurPartial PayPartial PayPartial PayPartial PayPartial PayPartial PayTotalTotalTotalAssessedMAILING Tax Line 1HomesteadDiscountFacePenalty
2Tax Payer 1Bill #PaidDFPCheckRemarkRemarkDATEAMOUNTDATEAMOUNTDATEAMOUNTDiscountFacePenaltyValueDescription1Description 2ADDRESSDescriptionDiscount 1Face 1Penalty 1Assessed ValueHomesteadCheckCheckCheck
Real Estate


first sort for date im thinking L (Partial Pay DATE) since it is the last payment possible.
L (Partial Pay DATE) since it is the last payment possible
then
j
then
h
then
c (if paid in full no payment plan)

if it can do this then it would also make sure original workbook is untouched
 
Upvote 0
I see. I think there would be two practical ways to combine the data: either VBA or Power Query. With either, you could leave the original data untouched and create a new worksheet that aggregates all of the source data into one table. I would add one column to that new table to show for each row what source file or year it came from. You could even pull in snapshots of the current 2022 worksheet. Then you could do any data cleaning and sorting necessary and reload the aggregated table to your workbook to support queries made by the Tax Cert Form worksheet.
 
Upvote 0
In looking over your most recent sample, and upon learning that the source data file may not always be sorted in the correct order, an entirely different approach is needed. The most recent information that you've shared illustrates why it is critically important to answer questions asked by those who are attempting to assist and to read carefully what they offer. In this case, I mentioned in Post #2 that the formula assumed the first match found in column U would be in the row of interest. Now it becomes apparent that this is not necessarily a valid assumption, and I wish you would have clarified then that the assumption was not valid. Doing so would have saved a lot of time, and you would not have been burdened with revising all of your formulas that use a MATCH construction to determine which row index in the data table to use. The following construction:
Excel Formula: 
MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0)
...is used in many of your formulas. None of them will work reliably because MATCH looks from the top down and upon finding the first match, that row is used. Unfortunately, that row may or may not have the most complete record of payments/dates. So the MATCH function is problematic, and to solve this problem with a formula requires a differnet approach...a very messy approach.
The revised approach finds all matching conditions in column U using the AGGREGATE function, and a count of the matching condition is determined using the COUNTIF function. The result of the AGGREGATE function is an array holding the row indexes where matches are found in column U. I'll call this array [A]. Then we extract data from the source table in array forrm consisting of columns H:M and only on the rows indicated by the row indexes from the AGGREGATE function. The result is an n x 6 matrix, where n is the number of rows where matches were found and 6 represents columns H through M. Each value in this matrix is tested to determine if the value is not equal to 0 (indicating the cell is not blank), and the resulting matrix is converted with the double unary operator to coerce TRUE's and FALSE's to 1's and 0's. This matrix is then multiplied by a vertical array of 1's of length 6 to generate a resultant array of length n having values equal to the number of elements on each row (of the extracted data array) that are not blank. I'll refer to this intermediate array as [C]. We want to identify the position of the maximum in [C] array because the corresponding row index for that position is where we will find the most complete set of partial payments/dates in the source data table. Unfortunately, older versions of Excel do not handle these next steps well because there is no way to save intermediate results. So all of the previously described steps need to be repeated so that the resulting array [C] can be fed into the MAX function to find the maximum element, and then by setting [C]=MAX[C] we obtain an array of n elements having values of either TRUE or FALSE, and the TRUE value indicates the postion where the most payment information will be found. We then multiple [A]*[C] and take the MAX to return the row index of interest. All of this is done in this part of the formula:
Excel Formula: 
MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) )
...and it is this expression that replaces the much smaller MATCH formula above. This approach can be implemented in Excel 365 much more easily as it has more powerful functions for handling the redundancies, operating on the arrays, and extracting data from the source table.

Looking further, I noticed the formula in D47 will never produce a result of "help". The four IF functions that are chained together can be reduced to two, but check the logic to determine the conditions under which a "help" message should delivered. As you have it written, D44 is either <= or > the SUM(D48:D50), therefore there is no way to reach the "help" result. I've suggested a revision in the mini-sheet here.

The B45 formula checks for certain conditions, and the last instruction is to return the following if all other logical tests result in FALSE:
Excel Formula: 
IF(INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)="","",INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1))
This is unnecessarily complex. This IF statement says to find the relevant row in the source table on the 'SK Real Estate 2021' sheet and insert the value from column C. But why check whether the value to be inserted is a blank if you are only going to insert a blank anyway? The entire IF statement can be replaced with:
Excel Formula: 
INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)
...that is until the MATCH component is swapped out for the replacement described above.

Here is a formula approach, with revisions to most of the formulas:
MrExcel_20220610.xlsx
ABCDE
12013-02-005-317
13
41
42SCHOOL 2021July/AugSept/OctNov/Dec
43Discount (-2%)Face:Penalty (+10%)
44PAID000
45Date Paid:10/18/2021HomeSteadAssessed Value
4601910
47PARTIALSDATE0<- SHORT IN FACE
48PAYMENT #110/29/202121.79FACE
49PAYMENT #29/15/202121.79FACE
50PAYMENT #310/18/202122.79PENALTY
Tax Cert Form
Cell Formulas
RangeFormula
C44C44=INDEX('SK Real Estate 2021'!$AE$2:$AE$7000,MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ))
D44D44=INDEX('SK Real Estate 2021'!$AF$2:$AF$7000,MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ))
E44E44=INDEX('SK Real Estate 2021'!$AG$2:$AG$7000,MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ))
B44B44=IF(B45="","UNPAID",IF(B45="EXONERATED","PER COUNTY",IF(B45="Tax Claims", "SHORT", "PAID")))
B45B45=IF(D47<=0,C50,IF(D44>D47,"Tax Claims",INDEX('SK Real Estate 2021'!$C$2:$C$7000,MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ))))
E46E46=INDEX('SK Real Estate 2021'!$T$2:$T$7000,MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ))
D46D46=INDEX('SK Real Estate 2021'!$AI$2:$AI$7000,MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ))
D47D47=IF(D48="","",IF(OR(SUM(D48:D50)>=D44,SUM(D44,-D48:D50)<0.02),0,SUM(D44,-D48:D50)))
C48:C50C48=IFERROR(IF(ROWS($C$48:C48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000, MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000, MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ),2*ROWS($C$48:C48)-1),""),"")
D48:D50D48=IFERROR(IF(ROWS($D$48:D48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000, MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000, MAX( AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))) * ( MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX('SK Real Estate 2021'!$H$2:$M$7000,AGGREGATE(15,6,(ROW($U$2:$U$7000)-ROW($U$2)+1)/('SK Real Estate 2021'!$U$2:$U$7000=$C$12),ROW(INDIRECT(1&":"&COUNTIF('SK Real Estate 2021'!$U$2:$U$7000,$C$12)))),{1,2,3,4,5,6})<>0),{1;1;1;1;1;1})) ) ),2*ROWS($D$48:D48)),""),"")
E48E48=IF(C48="","", "FACE")
E49:E50E49=IF(C49="","",IF($D$48<D49,"PENALTY","FACE"))

And here is sample data I used to confirm that the formulas would pull in information from the row holding the most information, regardless of its location in the table.
MrExcel_20220610.xlsx
HIJKLMNOPQRSTUVW
1
21
302
48/3/202180.688/30/202180.6610/29/202180.6680.66266.26700016-01-008-0143
57/30/2021161.479/14/2021161.47474.68161.45532.8111000011-01-003-1354
610/29/202121.7964.0721.7971.921810013-02-005-3175
706
87
98
109
1110/29/202121.799/15/202121.7964.0721.7971.921810013-02-005-31710
1211
1312
147/30/202176.589/14/202176.5710/18/202176.57225.1376.57252.698500012-02-004-05313
1510/29/202121.799/15/202121.7910/18/202122.7964.0721.7971.921910013-02-005-31714
167/12/202165.748/20/202165.7410/18/202165.74193.2865.74216.947600015-02-007-06715
1716
1817
198/3/202180.688/30/202180.6610/29/202180.6680.66266.26700016-01-008-01418
207/30/2021161.479/14/2021161.4710/29/2021161.45474.68161.45532.8115550011-01-003-13519
2110/29/202121.799/15/202121.7964.0721.7971.921810013-02-005-31720
2221
SK Real Estate 2021
Cell Formulas
RangeFormula
O3O3=SUM(O2664:O2707)
O7O7=SUM(O2709:O2711)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
3 payment some payments not made or paid in full
Replies
1
Views
399
CrashOD
C
C
  • Question
#Value! error when adding cells and - and is neg number
Replies
2
Views
422
CrashOD
C
C
  • Question
Coding for if then statement not working right
Replies
3
Views
373
CrashOD
C
C
  • Question
name(partial or not) and searches all the other sheets in workbook
Replies
0
Views
314
CrashOD
C
V
  • Solved
Incremental count of unique values from a dynamically generated list.
Replies
6
Views
346
vampsthevampyre
V

Forum statistics

Threads
1,215,276
Messages
6,124,007
Members
449,139
Latest member
sramesh1024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top