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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,553
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

scmcanada

New Member
Joined
Mar 5, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you so much Peter! I can't believe it was that simple.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,553
Office Version
  1. 365
Platform
  1. Windows
You're welcome. There are some great new functions available in 365. :biggrin:
 

Forum statistics

Threads
1,175,999
Messages
5,900,794
Members
434,854
Latest member
ExcelMuffin

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
Top