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 $200I 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

✔ 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 omegaDrag 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

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 C

message edited by dcutl002

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 tabelA 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 $200omega 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

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

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.

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

Thank you derbydad you are awesome, when i grow up i want to be just as knowledgeable as you with excel.

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

yess please i would like to know what combinacion of formulas you would need to construct, to make the above example able to work.

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 alphaand1000 beta, so we can't SUMIF on 1000 by itself. However, wecanSUMIF on1000alphaand1000beta. 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 omegaI 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 omegaNow 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

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?

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.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History