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 1in 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 2003message edited by debi110

See More: Merging two data sets.

✔ 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, 1050I 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.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 1This 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 Cmessage 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, 1050I 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.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...

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.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.

Report •