0

If I have two excel files:

files

...and I want to merge them depending on the header:

merge

How can I do that? I only can find solutions for when the headers are in the same order and all files contain the same amount of columns...

(These are just examples. In reality it's around 10 files with 150+ columns)

ms87
  • 1
  • 2
  • This really looks like a school homework. What have you tried so far? A dictionary would help you a lot -> https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure – Vityata Jul 17 '17 at 12:37
  • I just tried to use an easy example ;) It's actually a questionnaire with 150+ questions and it's for work. I've tried google since I hardly have any knowledge in coding and I can't think of a way to easily solve it with formula. As I said, I've found solutions for when the headers are consistent across sheets but not like here, where it's not always the same order and some columns are fully missing. – ms87 Jul 17 '17 at 14:15

1 Answers1

0

If you don't want to code anything make an empty 3rd spreadsheet with all the columns. Then when you've got that setup, insert blank columns into your both originals where the columns only exist in one sheet. Be sure what's in column A,B,C etc, etc is consistent across sheets.. Then copy paste ranges from files 1 and files 2 into file 3 (file 2 on first blank row after file 1 in file 3). Discard changes in files 1 and 2, and save file 3. Then you can try sorting data in whatever way suits..

JGFMK
  • 8,425
  • 4
  • 58
  • 92
  • I have like 10 files and they have 150+ columns so I was hoping to somehow find a way to avoid having to make these tasks manually. I'm fine with using code but I don't have enough experience with coding to even know where to start. I've been pondering if there's a solution to solve that with formula but I think it needs VBA or so? – ms87 Jul 17 '17 at 14:06
  • Rather than reading MSDN or one of those websites, that are a rather dry read, I'd suggest trying to find something on Youtube. A lot of people put excellent content on there, which I find enables me to absorb stuff quickly. https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5 - This looks pretty detailed. If your looking for something quicker try this https://www.youtube.com/playlist?list=PL3A6U40JUYCi4njVx59-vaUxYkG0yRO4m – JGFMK Jul 17 '17 at 14:51