Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Combine columns and find the sum for the matched columns from a different table

Subject: Combine columns and find the sum for the matched columns from a different table

From: sagar poudel

Date: 17 Apr, 2012 18:06:08

Message: 1 of 8

Hi
I am getting a problem while combining two columns (x, y) in a matrix (from first excel file). After the first step, I need to match the combined name (x,y) with similar name from a second matrix (from a second excel file). Then I need to find the sum of rows matching the name from the first matrix.
Here is an example.

Table 1 Table 2
1.2 1.4 1.2 1.3 20
1.2 1.6 1.2 1.4 30
1.2 1.8 1.2 1.6 20
1.3 1.4 1.3 1.4 35
1.3 1.6 1.3 1.8 40
1.3 1.9 1.3 1.9 20
                                                                            1.3 2.1 35

Here, the tables above have all numerical values. I have to match the columns 1 and 2 (x,y)(combined) from table 1 (all) with columns 1 and 2 (combined) (x,y) of Table 2. Then, based on perfect match, I have to find the sum of third column from table 2. In table 2, it is one of several sheets in the excel file. So I need to run similar process for all sheets so that I can get the sum of values (based on match) from each sheet and put it in a table as follows:

sheet1 30+20+35+20
sheet2 ...................
sheet 3 .....................

sheet n .....................

I don't need the sheet name, I just need a column that represents the sum for each sheet based on matched pairs of coordinates.

Again I have several excel files like this with multiple excel sheets (however sheets are with same name and equal number from each excel file). I need to match the same coordinates (x,y) for all of these files too. The final result should include one column from each excel file that represents the sum of values from each excel sheet (in that file) which is as follows:

                  excel file1 file2 filen
sheet 1 sum1 (105 as above) sum a1 sum a2
sheet 2 sum2 sum b1 sum b2


sheet n sum n sum n1 sum n2

Subject: Combine columns and find the sum for the matched columns from a different table

From: Bruno Luong

Date: 17 Apr, 2012 19:12:10

Message: 2 of 8

I count the word "Excel" has been mentioned 9 times in the post, and "Matlab" 0.

Is it a Matlab-related problem? I can't see any relation.

Bruno

Subject: Combine columns and find the sum for the matched columns from a different table

From: sagar poudel

Date: 17 Apr, 2012 19:32:05

Message: 3 of 8

Sorry for the confusion. Actually I am trying to get the final result using Matlab Programming while the available data are in excel files. I am stucked while looking for Matlab codes to combine the coordinates and find the sum in a formatted table using Matlab functions.

"Bruno Luong" <b.luong@fogale.findmycountry> wrote in message <jmkfaa$be8$1@newscl01ah.mathworks.com>...
> I count the word "Excel" has been mentioned 9 times in the post, and "Matlab" 0.
>
> Is it a Matlab-related problem? I can't see any relation.
>
> Bruno

Subject: Combine columns and find the sum for the matched columns from

From: dpb

Date: 17 Apr, 2012 19:36:37

Message: 4 of 8

On 4/17/2012 1:06 PM, sagar poudel wrote:
> Hi
> I am getting a problem while combining two columns (x, y) in a matrix
> (from first excel file). After the first step, I need to match the
> combined name (x,y) with similar name from a second matrix (from a
> second excel file). Then I need to find the sum of rows matching the
> name from the first matrix.
> Here is an example.
>
> Table 1 Table 2
> 1.2 1.4 1.2 1.3 20
> 1.2 1.6 1.2 1.4 30
> 1.2 1.8 1.2 1.6 20
> 1.3 1.4 1.3 1.4 35
> 1.3 1.6 1.3 1.8 40
> 1.3 1.9 1.3 1.9 20
> 1.3 2.1 35
>
> Here, the tables above have all numerical values. I have to match the
> columns 1 and 2 (x,y)(combined) from table 1 (all) with columns 1 and 2
> (combined) (x,y) of Table 2. Then, based on perfect match, I have to
> find the sum of third column from table 2....

As Bruno notes, the relationship to Matlab seems tenuous at best.
I _presume_ you're thinking of doing whatever it is that you want to do
w/ the data from Excel by either interacting w/ Excel from Matlab or
bring the data into Matlab from Excel and put the results back is the
connection.

Undoubtely somebody here can process the arrays as you wish if you can
explain precisely what it is that you mean in the above description.
Give an example of what you mean by "perfect match" and what the results
should be for the sample data.

Where a "name" comes from in the description I've no clue...

--

Subject: Combine columns and find the sum for the matched columns from

From: sagar poudel

Date: 17 Apr, 2012 19:49:14

Message: 5 of 8

Hi
Here, a perfect match is the match of coordinates (x,y) from table 1 and table 2 (first 2 columns). I am trying to read data from excel files using matlab and try to get the sum for the matching coordinates and write the output in a single text or excel file for further analysis.

Thank You.

dpb <none@non.net> wrote in message <jmkgo2$mb1$1@speranza.aioe.org>...
> On 4/17/2012 1:06 PM, sagar poudel wrote:
> > Hi
> > I am getting a problem while combining two columns (x, y) in a matrix
> > (from first excel file). After the first step, I need to match the
> > combined name (x,y) with similar name from a second matrix (from a
> > second excel file). Then I need to find the sum of rows matching the
> > name from the first matrix.
> > Here is an example.
> >
> > Table 1 Table 2
> > 1.2 1.4 1.2 1.3 20
> > 1.2 1.6 1.2 1.4 30
> > 1.2 1.8 1.2 1.6 20
> > 1.3 1.4 1.3 1.4 35
> > 1.3 1.6 1.3 1.8 40
> > 1.3 1.9 1.3 1.9 20
> > 1.3 2.1 35
> >
> > Here, the tables above have all numerical values. I have to match the
> > columns 1 and 2 (x,y)(combined) from table 1 (all) with columns 1 and 2
> > (combined) (x,y) of Table 2. Then, based on perfect match, I have to
> > find the sum of third column from table 2....
>
> As Bruno notes, the relationship to Matlab seems tenuous at best.
> I _presume_ you're thinking of doing whatever it is that you want to do
> w/ the data from Excel by either interacting w/ Excel from Matlab or
> bring the data into Matlab from Excel and put the results back is the
> connection.
>
> Undoubtely somebody here can process the arrays as you wish if you can
> explain precisely what it is that you mean in the above description.
> Give an example of what you mean by "perfect match" and what the results
> should be for the sample data.
>
> Where a "name" comes from in the description I've no clue...
>
> --

Subject: Combine columns and find the sum for the matched columns from

From: sagar poudel

Date: 17 Apr, 2012 20:13:14

Message: 6 of 8

Hi
The name in the tables are added to avoid confusion. These represent different sheet names in the excel files from where data is to be extracted using Matlab (I have a total of 26 sheets in each excel file with names sheet1, sheet 2....sheet 26). Final output without sheet name is needed where sum obtained from each sheet is arranged in a column. So it will be 26 rows for 26 sheets for an excel file.

"sagar poudel" wrote in message <jmkhfq$k0o$1@newscl01ah.mathworks.com>...
> Hi
> Here, a perfect match is the match of coordinates (x,y) from table 1 and table 2 (first 2 columns). I am trying to read data from excel files using matlab and try to get the sum for the matching coordinates and write the output in a single text or excel file for further analysis.
>
> Thank You.
>
> dpb <none@non.net> wrote in message <jmkgo2$mb1$1@speranza.aioe.org>...
> > On 4/17/2012 1:06 PM, sagar poudel wrote:
> > > Hi
> > > I am getting a problem while combining two columns (x, y) in a matrix
> > > (from first excel file). After the first step, I need to match the
> > > combined name (x,y) with similar name from a second matrix (from a
> > > second excel file). Then I need to find the sum of rows matching the
> > > name from the first matrix.
> > > Here is an example.
> > >
> > > Table 1 Table 2
> > > 1.2 1.4 1.2 1.3 20
> > > 1.2 1.6 1.2 1.4 30
> > > 1.2 1.8 1.2 1.6 20
> > > 1.3 1.4 1.3 1.4 35
> > > 1.3 1.6 1.3 1.8 40
> > > 1.3 1.9 1.3 1.9 20
> > > 1.3 2.1 35
> > >
> > > Here, the tables above have all numerical values. I have to match the
> > > columns 1 and 2 (x,y)(combined) from table 1 (all) with columns 1 and 2
> > > (combined) (x,y) of Table 2. Then, based on perfect match, I have to
> > > find the sum of third column from table 2....
> >
> > As Bruno notes, the relationship to Matlab seems tenuous at best.
> > I _presume_ you're thinking of doing whatever it is that you want to do
> > w/ the data from Excel by either interacting w/ Excel from Matlab or
> > bring the data into Matlab from Excel and put the results back is the
> > connection.
> >
> > Undoubtely somebody here can process the arrays as you wish if you can
> > explain precisely what it is that you mean in the above description.
> > Give an example of what you mean by "perfect match" and what the results
> > should be for the sample data.
> >
> > Where a "name" comes from in the description I've no clue...
> >
> > --

Subject: Combine columns and find the sum for the matched columns from a different table

From: Roger Stafford

Date: 17 Apr, 2012 20:51:05

Message: 7 of 8

"sagar poudel" wrote in message <jmkbeg$pdt$1@newscl01ah.mathworks.com>...
> I am getting a problem while combining two columns (x, y) in a matrix (from first excel file). After the first step, I need to match the combined name (x,y) with similar name from a second matrix (from a second excel file). Then I need to find the sum of rows matching the name from the first matrix. ........
- - - - - - - - -
  For each one of your "sheets" you can make use of matlab's 'bsxfun' function, provided you manage to create valid matlab numerical arrays for Table1 and Table2 from your excel data. Let Table1 and Table2 be such arrays with two and three columns, respectively. Then do:

 t = bsxfun(@eq,Table1(:,1),Table2(:,1).') & ...
     bsxfun(@eq,Table1(:,2),Table2(:,2).');
 s = sum(t,1)*Table2(:,3);

The quantity s would be the sum for the corresponding "sheet" (such as your 105.)

  Note: The above 'eq' requires exact equality. Even if your values differ by only the least bit in their 53-bit representation, they will be considered unequal.

  Note 2: If you want to have your problems in this newsgroup receive full attention, it would be wise to rephrase your description in matlab terms and avoid bringing in extraneous matters such as their "excel" source or "sheets" or the like.

Roger Stafford

Subject: Combine columns and find the sum for the matched columns from a different table

From: sagar poudel

Date: 17 Apr, 2012 21:21:11

Message: 8 of 8

Thank you for providing the solution. I appreciate it, at least solved my first part. However I have an additional problem of formatting it in the same file when I have multiple tables like above. e.g.

Table 3 Table 4
1.2 1.4 30 1.2 1.6 20
1.2 1.6 35 1.2 1.8 30
1.3 1.2 20 1.3 1.6 10
1.3 1.6 20 1.3 1.9 15

I need to format the output in a column using matlab which will give me the result in order for table 2, 3 and 4 respectively. Here is what I need in a column;

105 for table 2
30+35+20 95 for table 3
20+30+10+15 75 for table 4
.................
..................

I hope it may not be too confusing. If it can be done, I will save a lot of time while applying this matlab code to my real data. Thank you.

 

"Roger Stafford" wrote in message <jmkl3p$5b8$1@newscl01ah.mathworks.com>...
> "sagar poudel" wrote in message <jmkbeg$pdt$1@newscl01ah.mathworks.com>...
> > I am getting a problem while combining two columns (x, y) in a matrix (from first excel file). After the first step, I need to match the combined name (x,y) with similar name from a second matrix (from a second excel file). Then I need to find the sum of rows matching the name from the first matrix. ........
> - - - - - - - - -
> For each one of your "sheets" you can make use of matlab's 'bsxfun' function, provided you manage to create valid matlab numerical arrays for Table1 and Table2 from your excel data. Let Table1 and Table2 be such arrays with two and three columns, respectively. Then do:
>
> t = bsxfun(@eq,Table1(:,1),Table2(:,1).') & ...
> bsxfun(@eq,Table1(:,2),Table2(:,2).');
> s = sum(t,1)*Table2(:,3);
>
> The quantity s would be the sum for the corresponding "sheet" (such as your 105.)
>
> Note: The above 'eq' requires exact equality. Even if your values differ by only the least bit in their 53-bit representation, they will be considered unequal.
>
> Note 2: If you want to have your problems in this newsgroup receive full attention, it would be wise to rephrase your description in matlab terms and avoid bringing in extraneous matters such as their "excel" source or "sheets" or the like.
>
> Roger Stafford

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us