Re: Merging excel tables & more, countif, macro line insert etc

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,

Try this:

Select Table 1 and convert it to a Table (Ctrl+T) - please ensure that the "This table has headers" box is unchecked. Now assign it a name, say Range1. Now do the same for Table2 - convert it to a table and assign it a name, say Range2

Now save the file on the desktop. While on any blank cell, go to Data >
From Other Sources > From Microsoft Query. Select Excel files and click on
OK. In the Select Workbook window, select the file which you just saved on the desktop and click on OK. On the left had side box, you will see the two names which you had assigned - Table1 and Table2. Select Table1 and click on the right arrow - do the same for Table2 (All the columns of the two tables will appear in the RHS box). Now click on Next. You will get a message box saying that the Query Wizard cannot continue. Click on OK. You will now see the output you want (nearly). You may now click on any 1 entry in the duplicated column and then go to Records > Remove column. Now arrange the columns the way you like and go to File > Return Data to Microsoft Office Excel. Click on Properties and check the box for refresh every 1 minute. Also check the box for Refresh when opening the file. Click on OK and select the cell where you want the output. Click on OK.

You will now get your desired output.

Hope this helps.


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Cheekyaardvark" <robinraw@xxxxxxxxxxx> wrote in message news:35b6206b-17f6-404a-859b-9a106514ef81@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
So if we go with the above example & say that it was a static 2 way
split how would we get the merge to happen?

0300809 01 03 00006707 015
0300809 01 03 00008116 085

0300809 00012043
0300809 00012077

to get this
0300809 00012043 01 03 00006707 015
0300809 00012043 01 03 00008116 085
0300809 00012077 01 03 00006707 015
0300809 00012077 01 03 00008116 085

Is that now relatively straightforward

Thanks for all the help BTW

.

Relevant Pages

  • RE: how do I get a formula to automatically refresh thats a macro
    ... an empty cell on the sheet and type a number, any number, into it. ... ' of a cell, or, if OfText is true, of the Font in the cell. ... CountByColor = CountByColor - _ ... But I have to redrag it to get it to refresh after I highlight cells... ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Different Macro behavior when called from Immediate Window vs.
    ... Type text into cell A1. ... Send a workbook to my address below with your symbols, ... Whenever a .refresh is executed, ... Dim conn As String, newconn As String ...
    (microsoft.public.excel.programming)
  • Re: formula refresh problem
    ... Microsoft MVP - Excel ... They are being refreshed using the .CalculateFull method when a ... | cell contents of the formula and then pressing "enter" to refresh ... | formula within the single cell. ...
    (microsoft.public.excel.programming)
  • Re: Importing data question
    ... I think I would use another worksheet that contained the job number and the ... creates a comment in the correct Job number cell. ... > spreadsheet and how to refresh the data but my problem is I want to be ... > able to add comments/notes to each one (customer was called regarding ...
    (microsoft.public.excel.misc)
  • Re: Excel 2002/2003 vlookup crash while typing false argument
    ... In a cell all by itself? ... that the worksheet/workbook is corrupted and needs to be recreated. ... > Microsoft Office Excel can try to recover it for you. ... > I've sent the error report to Microsoft many times for about one year, ...
    (microsoft.public.excel.worksheet.functions)