Help

[John]

I have a database. 2 columns. I have one work sheet that needs to findest
the highest dollar amount during a period of time.

Example

10/1/2007
Company a 5
company b 10

10/2/2007
company b 12
company c 4
company a 1

10/3/2007
company a 15
company b 12
company c 3


So the worksheet would first need to look for all the ones that have company
A and then look for the highest number. So in this case it would have
happened on 10/3/2007 (15). I would think this would involve a vlookup, but
can't figure how to write it. Could someone lend some insight. Anything
will help
Thanks

 

[Jerry Whittle]

Databases don't have worksheets. Excel does. Also if you have two columns,
where does the date go?

Please show actual data with the proper field names.

 

[John]

Sorry. Everything is in Excel.
Date Total Credits Total Debits
Company A 10/1/07 125,000 200,000
Company A 10/2/07 75,500 125,000
Company B 10/2/07 500,000 1,000
Company A 10/3/07 400,000 100,000
Company B 10/5/07 200,000 200,000

Date is in Column B, Total Credits in C, Total Debits in D

So on a seperate sheet I have all the companies listed. I would like that
sheet to find the hightest Credit they ever had, and the highest Deibt they
ever had. Sometimes companies do not have credits/debits on a specific date.

Hopes this helps

 

[Jerry Whittle]

Is this Excel spreadsheet linked to Access? If not, this isn't the right
forum for the question.

 

[Lance]

Not the right place for this question.. but maybe this will help

In this sample I have a two 2 column spreadsheets named DATA and RESULTS.
Cells A1 and B1 of each are labeled LETTER and NUMBER. My data is a series
of single character letters and number strings. Just something to reference
when looking at the code.

sub top_letter

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Provider = "MSDASQL"
cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
"DBQ=C:\Book1.xls; ReadOnly=False;FirstRowHasNames=1;"
cn.Open

cn.Execute ("INSERT INTO [RESULTS$] ([LETTER], [NUMBER] ) SELECT TOP 1
[LETTER], [NUMBER] FROM [DATA$] WHERE [DATA$].[LETTER] = 'A' ORDER BY
[DATA$].[NUMBER] DESC")

cn.Close

end sub


Adjust the SQL string to match your tables and desired output.

I just noticed that your date values appear to be on individual rows? So
this probably won't work for you, if you want to treat your spreadsheet as a
database you need to format it like one.

Your best bet in the current form might simply be to write a little VBA
script to loop through the column containing your date until it find it, then
search for the highest value from there till you run into the end of your
date range.