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.

Improve this question
1
  • 1
    you might be able to do this with powerquery or powerpivot
    – PeterH
    Commented May 22, 2020 at 15:10

1 Answer 1

Reset to default
-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.

Improve this answer
5
  • This is far not an "automated way to create new sheet"
    – Máté Juhász
    Commented May 23, 2020 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
    Commented May 24, 2020 at 5: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
    Commented May 28, 2020 at 12:19
  • @user81805,, U need to write the suggested formula in cell I62 finish with Ctrl+Shift+Enter and fill it Right till column Age then Down. But remember you need to alter CELL REFERENCES in the formula according to the Data available on the Sheets you are using.
    – Rajesh Sinha
    Commented May 29, 2020 at 5: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
    Commented May 29, 2020 at 5:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .