Solved Merging two data sets.

August 3, 2015 at 09:53:27
Specs: Windows 7
good day guys,

I have two data sets and i am trying to merg this two data sets by counting the values in the two data sets. It looks likes this:

           A           B             C             D            E
          NR.   descript     location1      location2   location3  
     1   1000    alpha        $120          $300        $400       
     2   1000    alpha        $120           $300         $400                          
     3   1010    beta         $150            $450  .      $250
     4   1030    zeta          $600            $230         $200
                                  Dataset2
            F           G         H             I          J
          NR.     descript     location1      location2   location3      
      1   1000    alpha        $120          $300        $400                          
      2   1010    beta         $150          $450  .     $250
      3    1030    zeta         $600         $230        $200
      4    1050   omega         $600          $230        $200

I first tried to match column A dataset 1 with column A data set 2 en then used an index but this formula only allows to return one value. What i want to achieve is to match A1 WITH F1 if this that have the same number if this is true then i want to sum up the values C,D,E 1 WITH H,I,J 1
in a new table. there is also a NR. in data set 2 namely F4 (1050) that does not appear in data set 1, this row i want to include in mine new table. Can somebody help me please.I am using excel 2003


message edited by debi110


See More: Merging two data sets.

Report •

✔ Best Answer
August 4, 2015 at 12:26:40
It seems like you have a list of unique numbers in Column A and Column F:

1000, 1010, 1030, 1050

I would think that a SUM of 2 SUMIF functions should work:

For the merged table, put a list of your unique numbers starting in A11 and then use the formula shown in C11, etc.

       A          B                                     C    
10    NR       Descript                             Location 1
11   1000       alpha     =SUMIF($A$2:$A$5,A11,$C$2:$C$5)+SUMIF($F$2:$F$5,A11,$H$2:$H$5)
12   1010       beta
13   1030       zeta
14   1050       omega

Drag this down for Location 1 then adjust the [sum_range] arguments to match the columns for Location 2 & 3.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
August 3, 2015 at 13:35:25
I can help ypu partially, but I'll need more details.
What i want to achieve is to match A1 WITH F1 if this that have the same number if this is true then i want to sum up the values C,D,E 1 WITH H,I,J 1

This can be done by this formula:
=IF(A1=F1,C1+D1+E1+H1+I1+J1,"")

My question is...What value do you want to do if the statement is FALSE? If it is nothing, then this will work. If you want the number '0', then replace the double quotes "" with a 0.

there is also a NR. in data set 2 namely F4 (1050) that does not appear in data set 1, this row i want to include in mine new table.

Are we comparing more than 2 NR values? Are we comparing more than one line? Data set 1 is the only dataset with a matching 'NR' value (1000) , so dataset 2, 3, and 4 will be FALSE.

Don C

message edited by dcutl002


Report •

#2
August 3, 2015 at 14:21:30
thank you Don C,

so the tabel i want to construct would be a combination of data set 1 en data set 2.
This is what i have in mind: in a new tabel

 A           B             C             D            E
          NR.   descript     location1      location2   location3
           1000    alpha        $240          $600        $800   
           1010    beta         $300           $900  .     $500
           1030    zeta          $1200            $460     $400
           1050   omega         $600          $230        $200

omega would stay the same because there are no rows of 1050 omega in data set 1 so this row can be autamatically be copied. the other rows would be a sum of the same NR. and location. so for example NR. 1000 location1 in mine new tabel would be the sum of location1 in data set 1 en data set 2. Omega 1050 would not be a sum because there is only one row of it.

message edited by debi110


Report •

#3
August 3, 2015 at 18:19:27
1st question:

re: "I first tried to match column A dataset 1 with column A data set 2"

There is no column A in data set 2. Did you mean column F?

2nd question:

Why doesn't 1000 Alpha sum to these values?

Location 1     Location 2       Location 3
   $360          $900            $1,200 

message edited by DerbyDad03


Report •

Related Solutions

#4
August 3, 2015 at 21:18:10
Yes derbydad one row in dataset 1 of Alpha does not belong there. And indeed i meant to say column F. Thank you for the correction.

Report •

#5
August 4, 2015 at 12:26:40
✔ Best Answer
It seems like you have a list of unique numbers in Column A and Column F:

1000, 1010, 1030, 1050

I would think that a SUM of 2 SUMIF functions should work:

For the merged table, put a list of your unique numbers starting in A11 and then use the formula shown in C11, etc.

       A          B                                     C    
10    NR       Descript                             Location 1
11   1000       alpha     =SUMIF($A$2:$A$5,A11,$C$2:$C$5)+SUMIF($F$2:$F$5,A11,$H$2:$H$5)
12   1010       beta
13   1030       zeta
14   1050       omega

Drag this down for Location 1 then adjust the [sum_range] arguments to match the columns for Location 2 & 3.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#6
August 4, 2015 at 13:22:45
Thank you derbydad you are awesome, when i grow up i want to be just as knowledgeable as you with excel.

Report •

#7
August 4, 2015 at 16:05:02
I'm glad I could help. If you are interested, I could show you a trick to accomplish a similar task if your data sets looks like this:

         A          B          
1       1000      alpha
2       1010      alpha
3       1000      beta
4       1010      beta

You'll note that that data set does not have unique values in either Column A or Column B, meaning that the solution I offered above won't work.

But there is a method...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
August 4, 2015 at 21:49:21
yess please i would like to know what combinacion of formulas you would need to construct, to make the above example able to work.

Report •

#9
August 5, 2015 at 06:24:22
While there may be other methods to accomplish this goal, the method I am about to describe will introduce the concept of "Helper Columns".

A Helper Column is a column that can be used to store intermediate results that can help simplify further formulas. "Helper Column" is not an Excel term, so you won't find it in the Excel Help files. It is merely a term that has become common among Excel users. A Google search will bring up many different uses for Helper Columns.

Helper Columns can be placed anywhere they make sense and can even be hidden so that they don't impact the visual layout of the spreadsheet. In this case, I am going to include them right next to the 2 data sets, just to show you the concept.

In the table below, we don't have unique values that we can use the SUMIF function with. e.g. we have 1000 alpha and 1000 beta, so we can't SUMIF on 1000 by itself. However, we can SUMIF on 1000alpha and 1000beta. Note the elimination of the space.

Let's say we are starting with this:

         A          B       .....      F          G
1       1000      alpha               1000       beta
2       1010      alpha               1010       alpha
3       1000      beta                1000       alpha
4       1010      beta                1030       omega

I am going to insert a Helper Column at the beginning of each data set and use the Concatenation operator to combine the 2 values that we need to SUMIF.

           A       B         C   .....     G          H          I
1       =B1&C1   1000      alpha        =H1&I1       1000       beta
2       =B2&C2   1010      alpha        =H2&I2       1010       alpha
3       =B3&C3   1000      beta         =H3&I3       1000       alpha
4       =B4&C4   1010      beta         =H4&I4       1030       omega

Now I have a table that looks like this:

           A         B         C   .....      G             H          I
1       1000alpha   1000      alpha        1000beta        1000       beta
2       1010alpha   1010      alpha        1010alpha       1010       alpha
3       1000beta    1000      beta         1000alpha       1000       alpha
4       1010beta    1010      beta         1030omega       1030       omega

For the merged table, you will also create a Helper Column but it will contain the concatenated values - which are unique - and use the SUMIF function by referencing the Helper Columns (A and G) instead of the actual data columns (A:B and H:I).

e.g. =SUMIF($A$2:$A$4,A7,$D$2:$D$4)+SUMIF($G$2:$G$4,A7,$J$2:$J$4)

As noted earlier, the Helper Columns can all be hidden (including the one used for the merged data set) so that the user only sees "clean data" even though what is seen isn't what is actually being used by the formulas.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#10
August 5, 2015 at 11:35:19
thats a clever way to solve something that initially seems difficult to solve, excellent!
by the way derbydad what kind of books about excel to you read any suggestions?

Report •

#11
August 5, 2015 at 15:19:02
I've never read any Excel related books. I just play around and search the ole interweb for ideas. With a couple of decades of answering questions under my belt, I've taught myself a lot just by researching ways to solve other people's problems. That said, I am far from an expert when compared to some of the real gurus out there.

By the way, another great use of a Helper Column is to assist when sorting data in various configurations. Let's say you have a multi-column table and you want to play around with various sort criteria. If you add a column with nothing more than sequential numbers in it before you do any sorts, you can play around as much as you like and then return to the original configuration by simply sorting on the column of sequential numbers.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question