append - Combine 2 Excel tables into one appending the data? -


i have 2 tables on 2 separate sheets of ms excel 2007 workbook, below:

=========================== no.   f_name     l_name   =========================== 13   little     timmy 1   john       doe 17   baby       jessica ---------------------------   =========================== no.   f_name     l_name   =========================== 1   john       tim 16   kyle       joe 14   baby       katy 22   qbcd       wsde ---------------------------

both have same columns, can have different data.

i want combine data of both tables vertically i.e. single table data in 3rd separate sheet. if possible, want add column sheet name row came.

=================================== sheetname   no.   f_name     l_name   =================================== sheet1      13   little     timmy sheet1      1   john       doe sheet1      17   baby       jessica sheet2      1   john       tim sheet2      16   kyle       joe sheet2      14   baby       katy sheet2      22   qbcd       wsde -----------------------------------

can done without using macros?

you can activate office clipboard (arrow @ bottom right of clipboard section on ribbon home tab). copy both ranges use paste all command shown below.

you still need fill down sheet name in column first though can done double-clicking fill handle.

enter image description here

update

to same results formulas try filling down sheet name:

=if(row()<=counta(sheet1!a:a),"sheet1",if(row()<counta(sheet1:sheet2!a:a),"sheet2",""))

and fill down , across formula values in tables:

=if(row()<=counta(sheet1!a:a),sheet1!a2,if(row()<counta(sheet1:sheet2!a:a),index(sheet2!a:a,row()-counta(sheet1!a:a)+1),""))

Comments

Post a Comment

Popular posts from this blog

ubuntu - PHP script to find files of certain extensions in a directory, returns populated array when run in browser, but empty array when run from terminal -

i running os ububtu 16.04. ultimate goal able run php script cron job every minute. here php script located @ /var/www/html/tests/test/index.php : <?php $directorypath = $_server['document_root']."/tests/test/data/"; echo "directorypath: $directorypath<br><br>";//check $imagefilepathsarray = glob($directorypath . "*.{png,gif}", glob_brace); echo "<br><br>imagefilepathsarray: "; print_r($imagefilepathsarray);//check ?> when open php script in browser visiting url my-ip-address/tests/test/index.php , get: directorypath: /var/www/html/tests/test/data/ imagefilepathsarray:array( [0] => /var/www/html/tests/test/data/pic.png [1] => /var/www/html/tests/test/data/pic.gif ) before writing cron job in crontab file, need check if can run anywhere using terminal . opened ubuntu terminal @ /home/ location, , executed in terminal: sudo php /var/www/html/tests/test/index.php i got following out...
Read more

php - How can i create a user dashboard -

today have questions you, want create user dashboard site, include (obviously), users data, example -email -username -and others stuff how can information, , print them in html page? if can, how can these information via php sessions? shuld use session? example, if user login, , start new session ( session_start() ), how can data print in dashboard? should use php "project"? in advice, have day , thank time!
Read more

javascript - How to detect toggling of the fullscreen-toolbar in jQuery Mobile? -

i have jquery mobile page fixed full-screen toolbar, data-tap-toggle enabled. straight under toolbar have positioned banner should slide upwards when toolbar hiding , slide downward when toolbar showing. jquery mobile toggles toolbar applying , removing ui-fixed-hidden class - sadly, can't find in documentation of toolbar widget toggle , hide or show event that. how can detect when toolbar toggled, reposition banner? .banner { position: fixed; background-color: darkseagreen; top: 46px; min-height: 48px; width: 100%; text-align: center; line-height: 48px; } <!doctype html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"> <script src="https://code.jquery.com/jquery-1....
Read more