0

I need to combine data from two Excel sheets and I have found some good tutorials for using the Consolidate and Appending. These, however, do not seem to help in my case, which I try to explain in a simple manner:

  1. In one of the sheets, I would have 2 rows: cat and dog, and two columns: colour and size.
  2. In the second sheet I would have 3 rows: cat, dog and bird, and only one column: age.

Now I would like to make automatically a new sheet that would contain all the 3 animals on their (3) rows and their colour, size and age in 3 columns. Data concerning colour and size of the bird would naturally not be available in this merged table since the poor bird is not included in the first sheet.

Is there any way of doing this in a simple manner, without programming skills?

I should also mention that I found this discussion that seems to address a similar question: Merge Excel rows from two sheets. There is also a solution available, but as it requires some tweaking with the formula and the solution is already several years old, I thought to ask if the current Excel can do this in a more automated manner. Any help would be greatly appreciated.

user81805
  • 21
  • 4

1 Answers1

-1

Combining two lookup formula solves your problem:

enter image description here

How it works:

  • Enter headers in Sheet 3.

  • Formula in Sheet 3, Cell I62:

    =IFERROR(VLOOKUP(Sheet2!$I56,Sheet1!$I$50:$K$52,COLUMN(A1),FALSE),IFERROR(VLOOKUP(Sheet1!$I51,Sheet2!$I$56:$J$58,2,FALSE),""))

Adjust cell references in the formula as needed.

Rajesh Sinha
  • 8,995
  • 6
  • 15
  • 35
  • This is far not an "automated way to create new sheet" – Máté Juhász May 23 '20 at 10:58
  • @MátéJuhász,, you know better that Automation needs,, MACRO and the OP written *`Is there any way of doing this in a simple manner, without programming skills.?`* Then the only option left was, what I've shown,, plz post if U have any idea ,,, something different !! – Rajesh Sinha May 24 '20 at 05:22
  • Thanks @RajeshS! I'll try this. But I'd need a bit more advice: what about the formulas in 62J-L? I'm not quite sure how to proceed with them. And, a very basic question: I presume these data can be in different worksheets (i.e. the term "sheet" you use is not any specific Excel feature), right? – user81805 May 28 '20 at 12:19
  • @user81805,, U need to write the suggested formula in cell `I62` finish with **Ctrl+Shift+Enter** and fill it `R`ight till column Age then `D`own. But remember you need to alter CELL REFERENCES in the formula according to the Data available on the Sheets you are using. – Rajesh Sinha May 29 '20 at 05:22
  • **Cont,,** Now your second query,, about `SHEET`. Every workbook has Sheet to Sheets to work,, has name by default are `Sheet1` ,, `Sheet2` or the given name like `Account-2020`,, also known as TAB or TAB name,,, in the shown formula,, `Sheet2!$I56,Sheet2!$I$50:$K$52` the `Sheet2!` reads data from Sheet 2/TAB 2 from `I50 to K52`. Hope this help you,,, but first try what I've suggested. ☺ – Rajesh Sinha May 29 '20 at 05:22