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

  • Thread starter Thread starter Cheekyaardvark
  • Start date Start date
C

Cheekyaardvark

Hi
Hopefully someone can help me out there... I am really struggling with
this on.. I have 2 sets of data

0300809 01 03 00006707 015
0300809 01 03 00008116 085

Last column is percentages
I need to merge with this table

0300809 00012043 2
0300809 00012077 2
0300809 00012445 2
0300809 00012476 2
0300809 00015258 2
0300809 00018577 2

where last column is the number of % splits (I.E. 015 & 085) & this
may vary

What I need is to get this, I will have to insert lines based on the
count of the % & then find some way of combining
0300809 00012043 01 03 00006707 015
0300809 00012043 01 03 00008116 085
0300809 00012077 01 03 00006707 015
0300809 00012077 01 03 00008116 085
0300809 00012445 01 03 00006707 015
0300809 00012445 01 03 00008116 085
0300809 00012476 01 03 00006707 015
0300809 00012476 01 03 00008116 085
0300809 00015258 01 03 00006707 015
0300809 00015258 01 03 00008116 085
0300809 00018577 01 03 00006707 015
0300809 00018577 01 03 00008116 085

As I said I am really struggling with this one.. I have a load of data
that this needs to be done so manual method is not really feasible

Here's hoping!!
 
I am not sure ... I thought that you would need this to insert extra
lines in the sheet. I thought the answer here would be something along
the lines of looking up how many extra lines would be needed & then
filling with the necessary data. The 2 (in this instance is saying
that there are 2 lines needed as there is a 2 way split)
Lets simplify the above, maybe I confused by adding the number of
splits
Lets take these 2 tables
0300809 01 03 00006707 015
0300809 01 03 00008116 085


0300809 00012043
0300809 00012077

What I want is a method of starting with the second table & doing
this

0300809 00012043
INSERT LINE
0300809 00012077
INSERT LINE

FILL DOWN
0300809 00012043
0300809 00012043
0300809 00012077
0300809 00012077

MERGE FILES
0300809 00012043 01 03 00006707 015
0300809 00012043 01 03 00008116 085
0300809 00012077 01 03 00006707 015
0300809 00012077 01 03 00008116 085

in reality there may be 4 way splits, 6 way splits etc etc
Not sure if I am explaining this very well!!

I really need this solution in excel not access

Thanks
 
Hi,

It is quite simple to solve the problem if the number of % splits is the
same (2 in the example shown below). What is causing the problem is the
variable number of splits

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
So if I break out the files into seperate tabs where the % splits are
static then we can get a solution...
I.E. One tab where the for 2 splits, one for 3 way etc
 
Hi,

Yes, have one tab with all the data for the 2 way split. Have another where
we have data for 3 way slit. So we will have 2 output ranges, one for 2 ay
splits and another for 3 way splits.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
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
 
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
 
Back
Top