Combining Duplicate Rows?

[AllenRitch]

I have the following:

Account Desc 2004 2005
100200 CASH $3,000 $4,000
100200 CASH $2,000 $5,000
103500 BONDS $10,000 $6,000
104110 STOCK $1,000 $0

And would like to have:

Account Desc 2004 2005
100200 CASH $5,000 $9,000
103500 BONDS $10,000 $6,000
104110 STOCK $1,000 $0

Is this possible?

 

[lmcate2]

try dlookup

 

[lmcate2]

Are you using Excel?? Access???

 

[AllenRitch]

I've already tried VLookUp, but it will only pick the first match. DLookUp doesn't seem to be an option. I only have LookUp, VLookUp, & HLookUp.

 

[AllenRitch]

Sorry about that, I'm using Excel.

 

[AllenRitch]

P.S. I know that I can Subtotal, but unfortunately that won't work for what I need to do next.

 

[AllenRitch]

It seems like this should be a simple task in Excel. But I guess my only option is to import it into an Access database, group it by a query, then export back to Excel.

 

[Lilliabeth]

Data | Consolidate, choose a range - including the labels, hit the add button. Choose the next range, also including the labels and add...etc. Be sure that before you click Ok you check Use Labels in Left COlumn.

Please let me how it goes!

 

[AllenRitch]

I played with that option as well but kept getting an error stating there was an overlap problem. I'm not certain, but believe this feature is used to combine worksheets with like labels where my data is all on the same worksheet.

I ended up using an Access query to group & sum my data which worked pretty well. Thanks to all for your assistance.

 

[SkipVought]

Its a snap with a PivotTable report.

Can be done in about 5 seconds!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Lilliabeth]

Consolidate does work in a single worksheet and is very handy. I suspect the reason it didn't work for you is that you have labels in two columns, not just one.

 

[PeterMoran]

Hi,

If your account nos are unique and that is the basis of the lookup then you may be able to use SUMIF.

=SUMIF(range,criteria,sum_range)

Good Luck!

Peter Moran

 

[xlbo]

omg - Skip has it in 1 - this is a classic scenario for a pivottable - he's not joking when he says this can be done in about 5 seconds...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Select all data, do data / Pivot table and chart report, Hit Next / Next / Finish, Drag Account into where it says ROW fields, then Drag 2004 into DATA fields, and then drag 2005 on top of any of the numbers for 2005.

You will now see in B3 a grey field marked 'Data'. Click and hold and drag that to where it says 'Total' in cell C3

Now drag Desc BETWEEN the Account field in Col A, and the Data fields that start in Col B. It will look horrible as it will insert Totals, so just right click on any one of those totals, choose field settings, and then where it says subtotal on the left of the dialog box, just choose None.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------