|Sorry, we have a communications error.|
I was unaware that you had an actual Sheet 2,
I probably should have said to copy to a NEW sheet.
So if you already have a Sheet 1 & Sheet 2
copy the data to Sheet 3, the formula should still work.
Copy Columns A thru E from Sheet 1
( Order Number, Order Date, Order Type, Supplier Account Code)
of your data set to a NEW Sheet, do not copy the Amounts.
Then on the Data tab, select Remove Duplicates
Use only the Order Numbers or Column A
You should now have your three rows of unique Order numbers
with no Amounts and it should look something like this:
A B C D E
1) Order Number Order Date Order Type Supplier Account Code
2) 51300 12/1/2014 STANDARD SHC 101-566100-6101-1
3) 51302 12/1/2014 STANDARD MBC 101-7200-7036-1
4) 51312 12/1/2014 STANDARD APT 101-5400-6020-1
In cell F2 enter your array formula:
or the NON array formula:
Since I do not know how many unique Order numbers you have
Drag down as many rows as necessary.
message edited by mmcconaghy