Removing zero values from dynamic data

Hello,

Years ago, someone very kindly write an array formula that took data from a column and condensed it down, removing any zero values. Column A might have a 10-20 pieces of data in it, spread out down 150 rows. The remainder of the cells all have a zero value. The tricky thing is that column A would be dynamic, taking the data from another work sheet. The formula that was given to me was:

=INDEX(AI$170:AI$177, SMALL(IF(ISNUMBER(AI$170:AI$177)+ISERROR(AI$170:AI$177), numeric, ROW(AI$170:AI$177)-MIN(ROW(AI$170:AI$177))+1), ROW(1:1)))

This worked brilliantly, but I now need to do the same with a column of numbers and obviously the above won't work as it is for text. I have tried making some changes, but can't seem to get it to work.

If anyone is able to amend the formula above or write a new one to get it working, it would be brilliant!

Thank you.

Hello Glenn,

Thank you. The bit I am trying to do is only 8 cells, but I will copy it loads of times into different parts of the worksheet. I have attached the whole workbook because I wasn't sure if where the data came from would make a difference. Hope this isn't a pain.

The bit I am trying to solve is on the the "Workings" tab, cells AG169:AJ177

Just in case, zero values are hidden.

Thank you, thank you, thank you!

Brilliant, brilliant, brilliant!

Not only have you done what I needed but you have simplified it beautifully. Thank you so much.

Dear Glenn (or anyone else!),

I finished the spreadsheet that you kindly helped me with - please don't judge, I am sure you will hate the way that I did the rest of it, very clumsy! This spreadsheet was to help with food orders for a party in a hotel - automatically creating place cards with choices, a food table plan which lists the food for each table for the chefs and a display copy of the table plan.

I thought it was working perfectly, but there seems to be a problem with part of the spreadsheet –the kitchen order tab seems to be pulling through the wrong orders.

At first I thought it was a couple of tables (2 and 6) that must have been linking to the wrong cells; the rest of the tables seemed to be working fine, but when I changed the table numbers for the people sitting on table 2 to any other table number, the food order was still wrong.

For example, if you look at Sarah on table 1 (number 38 on the list) – it says she is having soup, but when this goes across the right (BY12) it says she is having Vinny. It is also wrong for her main course and dessert.

The formula that is pulling the data through is

=IFERROR(VLOOKUP($BY10,$B:$AB,COLUMNS($A:B),FALSE),"")

and then filled down.

The spreadsheet is attached. Did I get the formula wrong somehow? Any help would be wonderful!

Thanks, Jon

Hello Bo_Ry,

That is wonderful, thank you. It didn't occur to me that duplicates might be causing it. I have copied your formula and it is now working on the entire sheet.

Jon