amalgamate two lists into one

[Farley945]

Hi All,

I am unsure whether this can be done with formulas or if it has to be a macro but I would like to amalgamate two lists into one.

Some names will appear on both lists but the majority are in list 1.

See example attached.

I would like the lists to be in alphabetical order.

Both lists can have additional names added but these aren't frequent, this is also updated automatically already.

Any help would be appreciated.

[FlameRetired]

I saw this done a few years ago. I will attempt to find the thread again. No promises.

In the meantime please attend to (ie update) your forum profile. 19 is not a version. If you don't know how to do this click File > Account. The specific version is listed in the right hand frame.

There are features and functions available in newer versions. They may be helpful, but we need to know about them here and in future posts.

[FlameRetired]

Both lists can have additional names added but these aren't frequent, this is also updated automatically already.

That's rather cryptic. I would like clarification. How might it affect output? Should "additional names" be included in the output?

[Farley945]

Good Morning FlameRetired,

All the information is gathered from 300 or so spreadsheets which is picked up via a macro onto two different "Master Sheets"

If there is a new supplier added to the group this will be picked up when I run the Macro and all suppliers are in alphabetical order already.

I then use a simple =IF('Master'!$C4="","",'Master'!$C4) to pick up the supplier information and copy and paste down the sheet. This is list 1 and list 2.

Within list 3, column M, I would like amalgamate the two lists, N-Q I can use a vlookup to pick up the information.

You won't need to worry about the last table, I have that table to find out if there is a different in rebate and if there is, Excel will send an email.

Hope that helps and thank you for looking into it.

[FlameRetired]

With that said I am certain you will need VBA. Unfortunately I know very little VBA.

However I can move your thread to that sub-forum. Would you like me to do that?

For what it's worth I found that old formula solution. It in addition to being a resource heavy array formula it also requires re-dimensioning. It won't be helpful here.

[Farley945]

Hi FlameRetired

Yes please, thank you for looking into it for me though.