Create Dynamic List with Blank Rows in the Data

[Lewis Clark]

Hello Everyone!

I need to create some dynamic lists, but the data table has many blank cells. Please see the attached Excel file for sample data.

Information:
Account numbers are in text format. Account names are in the cells below the account numbers. The number of rows for each account will vary.
The number of blank cells in column A between accounts will vary.
The entire list will contain about 250 accounts that span about 3000 rows. The rows will usually contain data in other columns (not shown in this example).
For each account, the data needed to make the dynamic list will on the same row as the account number.

Goals:
1) Create a dynamic list containing the account number, account name, and balance for all accounts with a balance greater than zero.
2) Create a dynamic list containing the account number, account name, and status for all accounts with a status of "No".
3) Hide any cells in the dynamic list that generate error messages.

Thanks in advance for any assistance!

[Limor_OP]

Where is the numeric data?
you didnt add numbers...

[Lewis Clark]

Thanks for the fast reply! The numeric data is in column B in the sample file I attached to the original post..

[Limor_OP]

I meant where is the database (I read that the the original consists of 3000 lines but you can attach a sample that represents all variety that the formula will need to cover)

[Lewis Clark]

I thought I did attach a sample file. Looking at my original post, I see one attached file just below the line where I wrote Thanks in advance for any assistance.

I see a light blue box, with a paper clip and the words "Attached files" in black font. Just below this, there is a hyperlinked name "Dynamic List Example File.xlsx" in blue font. It mentiones the file size of 10.9 KB, 1 view, and a hyperlink to download.

Sorry if I'm doing something wrong. What am I missing?

[Lewis Clark]

The sample file is an abbreviated version of my actual file. I can expand a working example to cover the larger ranges in my real data. Thank you. : )

[Limor_OP]

You did attach a sample file but you didnt include your database within this file....you just displayed the requested results....

you're showing the dish without revealing the ingrdients
but nevermind - maybe one of the experts here will know how to help you by the look of the dish , and will guess what the ingredients are

[Lewis Clark]

Thanks for the clarification. Now I understand.

The original file did include the data, but did not show the desired results. I've added a second worksheet to the file that shows the desired results. Is this more clear?

Thank you. : )

[Limor_OP]

Great so here are the formulas you need:

A6:
=IFERROR(INDEX('Data file'!$A$1:$B$54,AGGREGATE(15,6,ROW('Data file'!$A$4:$A$64)/(('Data file'!$B$4:$B$64>0)),ROWS($E$1:E1)),1),"")

B6:
=IFERROR(INDEX('Data file'!$A$1:$B$54,AGGREGATE(15,6,ROW('Data file'!$A$4:$A$64)/(('Data file'!$B$4:$B$64>0)),ROWS($E$1:E1))+1,1),"")

C6:
=IFERROR(INDEX('Data file'!$A$1:$B$54,AGGREGATE(15,6,ROW('Data file'!$A$4:$A$64)/(('Data file'!$B$4:$B$64>0)),ROWS($E$1:E1)),2),"")

G6:
=IFERROR(INDEX('Data file'!$A$1:$B$54,AGGREGATE(15,6,ROW('Data file'!$A$4:$A$64)/(('Data file'!$C$4:$C$62="No")),ROWS($E$1:E1)),1),"")

H6:
=IFERROR(INDEX('Data file'!$A$1:$B$54,AGGREGATE(15,6,ROW('Data file'!$A$4:$A$64)/(('Data file'!$C$4:$C$62="No")),ROWS($E$1:E1))+1,1),"")

I6:
=IFERROR(INDEX('Data file'!$A$1:$C$54,AGGREGATE(15,6,ROW('Data file'!$A$4:$A$64)/(('Data file'!$C$4:$C$62="No")),ROWS($E$1:E1)),3),"")

[ChemistB]

For values > 0
Account

Please Login or Register  to view this content.

Name

Please Login or Register  to view this content.

Value

Please Login or Register  to view this content.

For second table
Account

Please Login or Register  to view this content.

Name

Please Login or Register  to view this content.

On Time

Please Login or Register  to view this content.

Belinda beat me to it.

[Lewis Clark]

Belinda & Chemist

Thank you both so much your your replies.

I'm working with Belinda's formulas since they were posted first. Your formulas worked great on the sample data, but I was not able to successfully adapt those formulas to my real database. I'm sure it is operator error on my part.

All of your formulas refer to three ranges on the Data Sheet worksheet. Could you please explain why each of these references use a different number of rows?

My real data is on a worksheet called Owner Accounts, in the range A12:P3000. Account numbers and names are in column A, balances are in column J, and the status is in column P.

Adapting your formulas to pull the account numbers and names from column A I'm using these, and they seem to be working correctly.
=IFERROR(INDEX('Owner Accounts'!$A$1:$J$3000,AGGREGATE(15,6,ROW('Owner Accounts'!$A$12:$A$3000)/(('Owner Accounts'!$J$12:$J$3000>0.001)),ROWS($E$1:E1)),1),"")

and

=IFERROR(INDEX('Owner Accounts'!$A$1:$J$3000,AGGREGATE(15,6,ROW('Owner Accounts'!$A$12:$A$3000)/(('Owner Accounts'!$J$12:$J$3000>0.001)),ROWS($E$1:E1))+1,1),"")


The formula I'm using to pull the balances from column J is not working correctly. I'm using:
=IFERROR(@INDEX('Owner Accounts'!$A$1:$J$3000,AGGREGATE(15,6,ROW('Owner Accounts'!$A$12:$A$3000)/(('Owner Accounts'!$J$12:$J$3000>0.001)),ROWS($E$1:E1)),2),"")

This formula is giving me all values of zero.

The formula I'm using to get the status is:
=IFERROR(INDEX('Owner Accounts'!$A$1:$P$3000,AGGREGATE(15,6,ROW('Owner Accounts'!$A$12:$A$3000)/(('Owner Accounts'!$P$12:$P$3000="Yes")),ROWS($E$1:E1)),3),"")

This is not giving me the "Yes" if the "yes" applies. But it is returning the index numbers of the first 18 records. There should be 18 records displaying a "Yes".

What am I doing wrong?

Thank you. : )

[Limor_OP]

Hi,
can you post your genuine file say only with the 50 first lines?
probably a problem with the range, need to see the true copy to decide...

Also you can leave it with the errors you mentioned in order to have a look

[Lewis Clark]

I've attached a sanitized version of the real workbook, to protect sensitive financial information. The data is on the Owner Accounts worksheet. I'd like the formulas for the report on the Treasurer Reports worksheet.

* Include every account that owes money (end balance greater than zero)

* For each of these accounts, indicate their status as shown in columns K through P on Owner Accounts. The On Time column may have yes or no. The other columns will either be yes, or be blank.

Thank you so much!

[JeteMc]

Try modifying the formula for C6 so that it reads:

Please Login or Register  to view this content.

Once entered then copy down and over. You'll notice that the formula produces several zero values which may be hidden using conditional formatting as in Cell value = 0 sets the font to white.
Let us know if you have any questions.

[Lewis Clark]

I was able to adapt the given examples to my data set. Thank you very much to all who replied!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.