Return a list from a table with multiple values per line

Hi Guys,

I am trying to create a new table (Output tab) from an existing table (Source tab). The source table has multiple values on each line and the new table needs to list these values as 1 per line while returning the values based on certain criteria.

The table where the data is coming from (Source tab) has the following applicable columns that need to be picked up in the new table:

1. Date Month of movement (Col G)
2. From (origin) of movement (Col J)
3. Class of movement (Col M to Col Z) - the data below the headings (M10:Z10) is the "NUMBER" being moved (column E in the new table) and is what we want to return in the new table but only 1 value (per class) on each row - currently the existing table can have multiple "class movements" per row

The other tricky part is that the existing table has all move types - Internal, Purchase, and Sale. The new table will only require Internal movements to come through. - THIS IS WHAT HAS MADE IT SOOOO DIFFICULT

I have been trying to use the AGGREGATE & SMALL function to create the new table (or better described as a list) however the fact that we only want to show Internal movements from a table that has all types of movements has got me stuck!

NOTE - column structures of the existing and new table cannot be changed (however can be added to outside the structure).

On the attached workbook I have added blue arrows to the existing table to indicate the columns that need to be populated on the new table. Also to help you further i have manually entered the data on the new table as it should appear when formulas are entered.

Any assistance will be much appreciated.

Cheers

Mike

Are you using columns A to E in the Source sheet for anything? Can I use one as a helper column? (Is this construed as "outside the structure"?)

Pete

Hi Pete, Yes you can. Feel free to use columns and rows to the left, right, above and below the table structure. Cheers

It's getting a bit late here, so I'll have to call it a day. I'll try to pick this up tomorrow if nobody else has chipped in overnight (for me).

My reference is 1259.

Pete

Ok thanks, talk tomorrow

Your'e a legend Bo, super efficient solution, thanks