Combine 2 spill lists to 1 long list - hair falling out

[greenstar]

Hi

These 2 formulas work ok on their own, which pull from named ranges, on multiple sheets on a different workbook. However I need them in 1 big list so I can use them with TRIMMEAN().

Please Login or Register  to view this content.

So if the first list produces 10k lines and the 2nd 5k lines. The final list should be 15k.

Both are returning a Duration from different sheets, which I format in [H]:MM.

Ideal solution: TRIMMEAN(MyBigList, 5%)


Any ideas appreciated!



What I have tried and not work
*Adding the above formulas into named ranges and creating another named range that references List1:List2
*Trying to feed both lines directly into TRIMMEAN() and named lists
*Trying VBA code that merge lists, but don't work with Spill lists

[Bernie Deitrick]

Try this: put a link to the first cell of your first list into a column - say, column M - in row 2 and copy down for 20000 rows, using

=IF(cell =""", "", cell)

Then put similar links to the first cell of your second list into the same column, starting at row 20001.

Then use this to combine the values into one list:

=FILTER(M:M,M:M<>"")

or just use

=TRIMMEAN(FILTER(M:M,M:M<>""),5%)

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[greenstar]

=FILTER(M:M,M:M<>"")

Thanks - although I gave an example of only 2 list, there are multiple which are filtered on multiple columns, producing several lists.

I only need to combine 2 at the time, but don't want to add duplicate 20k cells for each unique list

[greenstar]

Try this

Please Login or Register  to view this content.

Thanks but list 2 does not get appended to the end of List 1 using this, there is some kind of logical xor going on

Maybe I can better describe this as "JOIN" spill list1 and list2 back to back..

[kvsrinivasamurthy]

Pl upload a sample file. It is easy to think to get solution.

[Bernie Deitrick]

Then I would go with a power query - simple and effective.

[greenstar]

Attached is a sample workbook,

Please assume Sheet1 and Sheet2 are one workbook and Sheet3 separate workbook.

I want to select specific rows from Sheet1 and Sheet2 (based on certain criteria) and return the duration of said rows into a list I can feed into TRIMMEAN().

Sample made in Excel 2016 but actual workbook used in Excel 365.

[kvsrinivasamurthy]

Macro code for combined list

Please Login or Register  to view this content.

[greenstar]

Thank you for your help

Can I combine and feed the array in VBA to a Trimmean(), something like this?

CombinedList = WorksheetFunction.Transpose(M) + WorksheetFunction.Transpose(N)
Sheets("Sheet3").Range("E7").Value = WorksheetFunction.TrimMean(CombinedList, 0.05)

Thanks

[kvsrinivasamurthy]

TrimMean function will not work for Array.
Macro code:

Please Login or Register  to view this content.

[greenstar]

Thank you for your help!