0

Problem

I am looking for a way to stack values from multiple columns into one. I need to ensure that all values, including duplicates are added. I'm not too fussed if the order is changed, but all values need to be included. I'm aware of CONCAT, but I am not attempting to merge multiple columns into one row horizontally, I am attempting to merge them vertically.

Example Table

(I am unable to save this as a table, SO tells me I must format my code, despite it not being code. anyone who has permissions to change this back into a table, I would appreciate it)

A B A&B Merge
Foo Cheese Foo
Bar Oranges Bar
Baz Pear Baz
Qux Banana Qux
Quux Grape Quux
Corge Corge
Cheese
Oranges
Pear
Banana
Grape

Summary I am unsure on how to merge these two columns vertically into one column, instead of combining the data together (I always end up with FooBanana for example). Any assistance or guidance would be appreciated.

Extra

I am looking to filter formula the output column. I'm very familiar with FILTER, and I was just planning on running my FILTER on the output column, but if there's different way of approaching this problem accounting for the output needing to be put through FILTER, that would be great, but not necessary.

The stacking will need to be completed in multiple versions of Excel including 2016. The =FILTER work will be done exclusively within MSO 365.

Thanks!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Wisp
  • 198
  • 1
  • 11
  • Just the fact that you want to use FILTER makes this a vba problem as FILTER does not exist in Excel 2016 – Scott Craner May 18 '22 at 15:05
  • Here is a UDF that will stack ranges: https://stackoverflow.com/questions/68655098/replicate-google-sheets-array1-array2-function-in-microsoft-excel-as-user-def – Scott Craner May 18 '22 at 15:06
  • Sorry, I should have clarified further - I'll edit the question. The data itself needs to be merged in versions of Excel 2016, but the `=FILTER` work will be done exclusively in another sheet within MSO365, with no backwards compatibility needed. – Wisp May 18 '22 at 15:07
  • Stacking can be done with `VSTACK()` and/or `TOCOL()` – JvdV May 18 '22 at 15:23
  • ScottCraner - thank you. Although, due to some irritating policies, running macros generally isn't possible. I am looking for a formula that can accomplish this instead if possible. I tried to edit the question to specify a formula, but ran into the "question contains code" issue again. @JvdV - I don't seem to have access to these functions - company restricts the installed version to 2108, unfortunately. I suppose I'm out of luck here? Thanks again both for your help and suggestions. – Wisp May 19 '22 at 15:12

0 Answers0