Combine rows in Excel

[kbushnell]

I have a macro that I have created to format a worksheet for an import into another software. I have one more piece I would like to add but I am not sure how. I have four rows of data. A contains acct. numbers, B contains unique identifiers, C contains amounts, and D contains a date. What I need for a group of acct. numbers in column A combine any amounts in column C if column B begins with PN. For Example:
3050092010 831681101 15.223 11/14/2006
3050092010 939330106 3175.766 11/14/2006
3050092010 PN6009230 336.24 11/14/2006
3050092010 PN6009776 389.45 11/14/2006
3050092010 PN6009784 4257.9 11/14/2006
3050100047 831681101 1097.502 11/14/2006
3050100047 939330106 619.594 11/14/2006
3050100047 PN6010477 1956.29 11/14/2006
3050100047 PN6010691 7108.36 11/14/2006
The macro would return:
3050092010 831681101 15.223 11/14/2006
3050092010 939330106 3175.766 11/14/2006
3050092010 Loan 4983.59 11/14/2006
3050100047 831681101 1097.502 11/14/2006
3050100047 939330106 619.594 11/14/2006
3050100047 Loan 9064.65 11/14/2006

So it would take the total PN's from column B for each Acct. ID in column and make one row with a total that says Loan. Any help is greatly appreciated.
I posted this question last week but the post must have been deleted because it is not found now.

 

[GlennUK]

You mean you have 4 columns of data. How is the date chosen for the grouped PN row?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[kbushnell]

Yes, sorry I mean't four columns not rows. The date will be the same for all of the rows including the PN rows.

 

[SkipVought]

Hi,

No need for a macro. Use MS Query, via Data/get External Data/New database Query -- Excel Files -- YOUR WORKBOOK....

Assuming the headings are...
Account, ID, Amount, T_Date

the SQL in the query grid is

Select
  Account
, iif(left([ID],2='PN','LOAN',[ID])
, Sum([Amount])
, T_Date
From [i]YourSheetName[/i]$
Group By 
  Account
, iif(left([ID],2='PN','LOAN',[ID])
, T_Date

Skip,

_[red][/red]