Extracting Non-zero data into list with description column

[scmcanada]

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
	A	B	C							J	K	L	M	N	O	P
1	Non Pro-rated Adjustments - Credit Buyer
2	Credit to buyer for title insurance	$ 315.00	dropdown								Credit to buyer for title insurance	$ 315.00		Credit to buyer for title insurance		315
3		$ -	type if needed								FALSE	$ -				20
4		$ -	type if needed								FALSE	$ -				25
5
6	Non Pro-rated Adjustments - Credit Seller
7		$ 20.00	type if needed								0	$ 20.00
8		$ -	type if needed								FALSE	$ -
9		$ -	type if needed								FALSE	$ -
10
11
12	Monthly Adjustments
13	Monthly Adjustment 1
14	Condo Fees		dropdown
15	Seller Paid	$ 50.00
16	Monthly Amount Payable	$ 50.00	use Payable OR Receivable, not both
17	Seller Received	$ -
18	Monthly Amount Receivable	$ -	use Payable OR Receivable, not both
19	Completion Date	February 14, 2022
20	Number of Days	14
21	Days in Month	28
22	Pro-rated Payable	$ 25.00
23	Pro-rated Receivable	$ -
24	Credit Buyer (payable)	$ -									FALSE	$ -
25	Credit Seller (payable)	$ 25.00									Condo Fees	$ 25.00
26	Credit Buyer (receivable)	$ -									FALSE	$ -
27	Credit Seller (receivable)	$ -									FALSE	$ -
SOACalcs

Cell Formulas
Range	Formula
K2:K4,K7:K9	K2	=IF(B2>0,A2)
L2:L4,L24:L27,L7:L9	L2	=IF(B2>0,B2,0)
N2	N2	=INDEX($K$2:$K$111,MATCH(TRUE,INDEX($L$2:$L$111<>0,),0))
P2:P12	P2	=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))))
K24	K24	=IF(B24>0,A14)
K25	K25	=IF(B25>0,A14)
K26	K26	=IF(B26>0,A14)
K27	K27	=IF(B27>0,A14)
B19	B19	='[Financial Worksheets - Signing Only.xlsx]Info'!$B$4
B20	B20	=B19-(EOMONTH(B19,-1)+1)+1
B21	B21	=DAY(EOMONTH(B19,0))
B22	B22	=B16*B20/B21
B23	B23	=B18*B20/B21
B24	B24	=IF(B15>B22,0,B22-B15)
B25	B25	=IF(B24=0,B15-B22,0)
B26	B26	=IF(B17>B23,B17-B23,0)
B27	B27	=IF(B26=0,B23-B17,0)

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=#REF!
A14	List	=#REF!

 

[Peter_SSs]

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
	K	L	M	N	O	P
1
2	Credit to buyer for title insurance	$315.00		Credit to buyer for title insurance		315
3	FALSE	0		0		20
4	FALSE	0		Condo Fees		25
5
6
7	0	$20.00
8	FALSE	0
9	FALSE	0
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24	FALSE
25	Condo Fees	$25.00
26	FALSE
27	FALSE
28
FILTER

Cell Formulas
Range	Formula
N2:N4	N2	=FILTER(K2:K28,L2:L28<>0,"")
P2:P4	P2	=FILTER(L2:L28,L2:L28<>0,"")
Dynamic array formulas.

 

[scmcanada]

Thank you so much Peter! I can't believe it was that simple.

 

[Peter_SSs]

You're welcome. There are some great new functions available in 365.