Using Array Formulas to merge datas to one master sheet

[Mahir Yagimli]

Hello Excel Ninja's,

Hope you can help me out on this one.

I have one master sheet and two employees shall work on it simultaneously. I thought of an array formula to solve the issue.

Employee A would insert his datas to sheet a and Employee B to sheet B. Now the array formula would be in the master sheet merging the datas from sheet a and sheet b. for example I have a formula in Master_Sheet! A1 - IF(AND(Sheet_A!A1<>"";Sheet_B!A1<>"";Sheet_A!A1;"Put the other result from Sheet_B!A1 into the cell in Master Sheet!A2 and so on.

Is that possible? In the Internet I found an array which is like follows:
IF(IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1,ROW()-SUM( IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))=0,"", IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1, ROW()-SUM(IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1)))

The Problem with that is takes all the results of the first sheet and then it shifts to the other sheet.

I'd like to let excel make a choice between a1 from sheet a and sheet b and put the other result one row below.

Thank you in advance for your help.

Kind regards

Mahir

 

[r2c2]

@Mahir Yagimli

Can you tell us what raw data you get and how you want the merged output to be?

If it is a simple concatenation of two sets of dates, you can use Power Query to set up a merged table. Whenever users change data, just refresh the PQ table and see all dates in one place.

If that is not the case, please provide an example workbook with what you need.

Thanks,

 

[Mahir Yagimli]

@r2c2

Thank you for your help and idea. I've tried the array formula above it worked out fine, but it would not take the original formating and the links of the original data.

For Power Query I will need to get the OK of our IT department. So this option is on hold for the moment.

I've uploaded an example workbook with the array formula.

If it is not possible to show the data with original formatting and the exact hyperlinks of the original data, is there a possibility to merge two workbooks without changing the formating and keeping the hyperlinks of the original data.

When merging workbooks with the same header columns, I have the problem, that I need to define a range. Instead I'd like to copy&paste as many rows as are actually filled out. Any ideas on that?

Thanks in advance for every helping member.

Kind regards

Mahir Yagimli