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
Bookmarks