Combine rows of data in a worksheet

[Guest]

I am trying to combine data from two companies into one
spreadsheet for the complex. I need to combine the rows
that contain similar data for that time period. For
example, since we are looking at 2 separate companies in
the division, we may have 2 separate entries for customer
# 1009 for fiscal code 04/04. We would like to see just
one entry made for the entire complex. Is there a formula,
or code to do this? I have been trying some things, but
none of them seem to work.

 

[Frank Kabel]

Hi
and how do you want them combined. You may post an example (plain
text - no attachment please)

 

[Tom Ogilvy]

If you created a list of unique customer id's, then you could retrieve the
data from each companies data using vlookup or a combination of index and
match (if the customer id isn't the leftmost column of the data of
interest). You would have a vlookup formula in each of two columns, with
one column showing data from company 1 and the second showing data from
company 2 ( a column per company for each data item returned). If the data
is number and you want to sum the data, you could combine them with an if
formula

=if(isna(vlookupcompany1),0,vlookupcompany1) +
if(isna(vlookupcompany2),0,vlookupcompany2)

as an example.

Other things to look at are Data=>Consolidate, and pivot table report under
the data menu as well.

 

[Joanne]

Cust Id Name Fiscal Code MSF Sales$
1009 abc 04/04 300 5000
1035 XYZ 03/04 250 3500
1009 abc 04/04 400 4500

I would like to combine the data under the following
conditions: If the customer # is the same, as well as the
fiscal code, We would like the MSF and Sales $ figures to
be combined so that we only have one row of data for the
fiscal period. We do business for the same customers at
both plants, and would like to see the totals for the
customer, not the plant.

Thanks for your help.

 

[Joanne]

The data has been merged into one large spreadsheet, from
the companies 2 databases. therefore, I am looking to find
all instances of the same customer number in the cust id
column , and for the same fiscal period and merge them
into a single row of data. I have tried the data
consolidation, but it isn't working for me. It gives me no
results.

Thanks

 

[Frank Kabel]

Hi
for this have a look at piot tables to aggregate your data. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Joanne]

I thought that would work, but unfortunately, my manager
does not want to use pivot tables. THat is why I am
looking at alternate methods.

 

[Frank Kabel]

Hi
you should talk to your manager
all< other solutions would require complex formulas or VBA:
- first extracting a unique list of customers/fiscal years
- using SUMPRODUCT to sum all your values accordingly

 

[Joanne]

Thanks

-----Original Message-----
Hi
you should talk to your manager
all< other solutions would require complex formulas or VBA:
- first extracting a unique list of customers/fiscal years
- using SUMPRODUCT to sum all your values accordingly


--
Regards
Frank Kabel
Frankfurt, Germany



.