how to unit data in different sheet into one sheet?

  • Thread starter Thread starter quietning
  • Start date Start date
Q

quietning

I have same style data in differet sheet:

sheet1
q ww
w ww
e r
r e
t r
(and adding)

sheet2
5 f
6 g
d h
e h
g juh
(adding)

and in sheet3 I want automatic display:

q ww
w ww
e r
r e
t r
5 f
6 g
d h
e h
g juh

any solutions?
 
One play via non-array formulas ..

In Sheet1, data is assumed in cols A & B, from row1 down
Use an empty col to the right, say col E
Put in E1: =IF(COUNTBLANK(A1:B1)=2,"",ROW())
Copy E1 down to say, E100, to cover the max expected data in cols A & B

In Sheet2, data is assumed in cols A & B, from row1 down
Use an empty col to the right, say col E
Put in E1: =IF(COUNTBLANK(A1:B1)=2,"",MAX(Sheet1!E:E)+ROW())
Copy E1 down to say, E100, to cover the max expected data in cols A & B

In Sheet3
----
Put in A1:
=IF(ROWS($A$1:A1)>MAX(Sheet1!$E:$E),INDEX(Sheet2!A:A,MATCH(SMALL(Sheet2!$E:$
E,ROWS($A$1:A1)-MAX(Sheet1!$E:$E)),Sheet2!$E:$E,0)),INDEX(Sheet1!A:A,MATCH(S
MALL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A1 across to B1, fill down to B200
(i.e. cover a combined range size of the max data expected in Sheets 1 & 2)

You'd get the desired results in cols A & B
(but with #NUM! lines below)

For a cleaner looking output,
we could use conditional formatting to mask / hide the #NUM! lines

Select cols A & B
Click Format > Conditional Formatting
Under Condition 1, put it as:
Formula is: =ISERROR(A1)
Click Format button > Font tab > Color: White* > OK
*choose a font color which is the same as the fill color
Click OK at the main dialog
 
In Sheet3 :

For an even cleaner look,
we could also suppress the display of extraneous zeros in the sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

Extraneous zeros would display in Sheet3 for lines where only one cell out
of 2 (per line) were filled in cols A and B in the source Sheets 1 and 2.

For e.g.: if in Sheet1, only A1 contains an input say: X, B1 is left blank,
then Sheet3's A1 will return: X, while Sheet3's B1 will return: 0
 
Back
Top