Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert etc
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert etc
Cheekyaardvark wrote:
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!! This can be done pretty easily using a SQL platform, such as Access or MS Query. One question I have though is how significant is it that the "number of % splits" indicated in the second table corresponds to the actual number of splits in the first table? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert etc
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert etc
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 "Cheekyaardvark" wrote in message ... 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!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert etc
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert 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 "Cheekyaardvark" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert etc
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging excel tables & more, countif, macro line insert etc
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert a line via macro | Excel Discussion (Misc queries) | |||
macro to insert blank line when lines sum to zero | Excel Discussion (Misc queries) | |||
Insert blank line macro | Excel Worksheet Functions | |||
Macro Line Insert | Excel Discussion (Misc queries) | |||
Insert Line Macro | Excel Discussion (Misc queries) |