Extracting Non-zero data into list with description column

scmcanada

New Member
Joined
Mar 5, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am working on a formula to pull non zero data from specific cells and turn it into a list in the same order it was originally in. I also need the description from a different column to be returned beside the cell. I've been successful with returning the numbers I need using this formula found in Cells P3:P12 - =IF(ROWS(P$2:P2)>COUNTIF(L$2:L$28,">0"),"",INDEX(L$2:L$28,SMALL(IF(L$2:L$28>0,ROW(L$2:L$28)-ROW(L$2)+1),ROWS(P$2:P2))))

I have found a way to get the first description to match using this formula in N2 using this: =INDEX($K$2:$K$111,MATCH(TRUE,INDEX($L$2:$L$111<>0,),0)) but I can't bridge the gap and get a formula that will fill down in column N to match up to each number in column P.

I've attached a small sample but the real sheet has many more adjustment calculations and the output from this tab will flow to another sheet that is a formal document used for clients.

I'm sure I'm missing something simple and hopefully someone can point me in the direction.

Thanks in advance!

Test.xlsx
ABCJKLMNOP
1Non Pro-rated Adjustments - Credit Buyer
2Credit to buyer for title insurance$ 315.00dropdownCredit to buyer for title insurance$ 315.00Credit to buyer for title insurance315
3$ -type if neededFALSE$ -20
4$ -type if neededFALSE$ -25
5 
6Non Pro-rated Adjustments - Credit Seller 
7$ 20.00type if needed0$ 20.00 
8$ -type if neededFALSE$ - 
9$ -type if neededFALSE$ - 
10 
11 
12Monthly Adjustments 
13Monthly Adjustment 1
14Condo Feesdropdown
15Seller Paid$ 50.00
16Monthly Amount Payable$ 50.00use Payable OR Receivable, not both
17Seller Received$ -
18Monthly Amount Receivable$ -use Payable OR Receivable, not both
19Completion DateFebruary 14, 2022
20Number of Days14
21Days in Month28
22Pro-rated Payable$ 25.00
23Pro-rated Receivable$ -
24Credit Buyer (payable)$ -FALSE$ -
25Credit Seller (payable)$ 25.00Condo Fees$ 25.00
26Credit Buyer (receivable)$ -FALSE$ -
27Credit Seller (receivable)$ -FALSE$ -
SOACalcs
Cell Formulas
RangeFormula
K2:K4,K7:K9K2=IF(B2>0,A2)
L2:L4,L24:L27,L7:L9L2=IF(B2>0,B2,0)
N2N2=INDEX($K$2:$K$111,MATCH(TRUE,INDEX($L$2:$L$111<>0,),0))
P2:P12P2=IF(ROWS(P$2:P2)>COUNTIF(L$2:L$28,">0"),"",INDEX(L$2:L$28,SMALL(IF(L$2:L$28>0,ROW(L$2:L$28)-ROW(L$2)+1),ROWS(P$2:P2))))
K24K24=IF(B24>0,A14)
K25K25=IF(B25>0,A14)
K26K26=IF(B26>0,A14)
K27K27=IF(B27>0,A14)
B19B19='[Financial Worksheets - Signing Only.xlsx]Info'!$B$4
B20B20=B19-(EOMONTH(B19,-1)+1)+1
B21B21=DAY(EOMONTH(B19,0))
B22B22=B16*B20/B21
B23B23=B18*B20/B21
B24B24=IF(B15>B22,0,B22-B15)
B25B25=IF(B24=0,B15-B22,0)
B26B26=IF(B17>B23,B17-B23,0)
B27B27=IF(B26=0,B23-B17,0)
Cells with Data Validation
CellAllowCriteria
A2List=#REF!
A14List=#REF!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to the MrExcel board!

Since you have MS 365 you have some other formula resources available to you. Try these formulas (no need to copy down at all)

22 03 06.xlsm
KLMNOP
1
2Credit to buyer for title insurance$315.00Credit to buyer for title insurance315
3FALSE0020
4FALSE0Condo Fees25
5
6
70$20.00
8FALSE0
9FALSE0
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24FALSE
25Condo Fees$25.00
26FALSE
27FALSE
28
FILTER
Cell Formulas
RangeFormula
N2:N4N2=FILTER(K2:K28,L2:L28<>0,"")
P2:P4P2=FILTER(L2:L28,L2:L28<>0,"")
Dynamic array formulas.
 
Upvote 0
Solution
You're welcome. There are some great new functions available in 365. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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