Filtering rates from rows

Drewgarry007 · Oct 1, 2011

Hi there,
I have a list of exchange rates in column D in sheet1 and have a second list of exchange rates in column Q in sheet2 in the same workbook.

I need a vba code that will allow me to compare the two lists of rates and if there is a rate in column D sheet1 that does not exist in column Q sheet2 then I want to delete that entire row in sheet1.

Can you pls help? Is there some sort of loop or vlookup I can use?

Many thanks!

Peter_SSs · Oct 1, 2011

Welcome to the MrExcel board!

Unless your lists are very large, I think this should do what you want. It assumes a heading in D1 of Sheet1.

Test in a copy of your workbook.

 Sub CheckRatesList() Application.ScreenUpdating = False With Sheets("Sheet1") .Columns("D").Insert With .Range("D2:D" & .Range("E" & .Rows.Count).End(xlUp).Row) .Formula = "=IF(COUNTIF('Sheet2'!Q:Q,E2),1,"""")" .Value = .Value On Error Resume Next .SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 End With .Columns("D").Delete End With Application.ScreenUpdating = True End Sub

Drewgarry007 · Oct 3, 2011

Hi,

This formula deletes all of the rows in sheet1? any ideas why that would be?

Peter_SSs · Oct 3, 2011

Drewgarry007 said:
Hi,

This formula deletes all of the rows in sheet1? any ideas why that would be?

The most obvious answer would be that none of the Sheet1 values appear on Sheet2 and the macro has just done its job.

Are you sure you haven't altered the columns in one or both the sheets? That is, still using column D in Sheet1 and column Q in Sheet2?

Using another copy of your workbook, find a value in column D of Sheet1 that you believe does exist in column Q of Sheet2 and note each cell. Then in a vacant cell, try this formula, changing the cell references to those just noted.

=EXACT(Sheet1!D6,Sheet2!Q5)

Given that everything was deleted, I'm assuming this will return FALSE when you would expect TRUE. If it is FALSE, then try these, again adjusting the cell reference to the same two cells.

=LEN(Sheet1!D6)
=LEN(Sheet2!Q5)

If these return different values, then the likely culprit is trailing (or leading) spaces in the cell that returned the largest length.

Drewgarry007 · Oct 4, 2011

Ha my mistake, the rates in sheet1 where in column "I" not "D"... I have there for adjusted your formula as follows and it works!!! awesome!!

Application.ScreenUpdating = False
With Sheets("Sheet1")
.Columns("I").Insert
With .Range("I2:I" & .Range("J" & .Rows.Count).End(xlUp).Row)
.Formula = "=IF(COUNTIF('Sheet2'!Q:Q,J2),1,"""")"
.Value = .Value
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With
.Columns("I").Delete
End With
Application.ScreenUpdating = True

Now that we have only the rates that we want in sheet1, I need do a secondary check:
For every rate in sheet2 col Q there could be a number of the same rates in sheet1 col I see below:

<TABLE style="WIDTH: 310pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=414 x:str><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=68>Dealt CCY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=88>Dealt Amt</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=83>Counter CCY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=80>Counter Amt</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=95>Customer Rate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=17>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num>-540000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="0.96079300000000001">0.960793</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=17>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="-3260000">-3260000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="0.96079300000000001">0.960793</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="3170000">3170000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="2620000">2620000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="2540000">2540000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="6520000">6520000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num>47510000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="12440000">12440000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num>190000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR></TBODY></TABLE>

Now I want to check that the above total "dealt amt" (sheet1, col F)adds up to the ralated amount in sheet2... the problem is that the total notionals in sheet2 could be either in (col "K") or (col "O")

<TABLE style="WIDTH: 348pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=464 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 50pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=66>Currency1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=112>Currency1Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=66>Currency2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=112>Currency2Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>DealtRate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 align=right x:num="3955066.28">3,955,066.28</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28 align=right x:num="-3800000">-3,800,000.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 align=right x:num="0.96079300000000001">0.960793</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ff99cc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28 align=right x:num="75000000">75,000,000.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="-72083025">-72,083,025.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.96110700000000004">0.961107</TD></TR></TBODY></TABLE>

How do you suggest I can check this?

Many thanks for your help so far!! you are a genius!

Drew

Peter_SSs · Oct 6, 2011

1. Where/how do you want the results reported?
a) On every row of Sheet2? If so what column (R?)?
b) On Sheet1 next to the first occurrance of each rate, say like below? If so, confirm that we can use column J or advise what column.
c) Some other way? If so, details please.

Excel Workbook

E

F

G

H

I

J

1

Dealt CCY

Dealt Amt

Counter CCY

Counter Amt

Customer Rate

Check Totals

2

USD

-540000

AUD

0.960793

OK

3

USD

-3260000

AUD

0.960793

4

AUD

3170000

USD

0.961107

OK

5

AUD

2620000

USD

0.961107

6

AUD

2540000

USD

0.961107

7

AUD

6520000

USD

0.961107

8

AUD

47510000

USD

0.961107

9

AUD

12440000

USD

0.961107

10

AUD

10000

USD

0.961107

11

AUD

190000

USD

0.961107

Sheet1

2. What version of Excel are you using?

3. Approximately how many rows of data might there be on each sheet (after the rows have been deleted by the previous macro)?
I'm just looking for ballpark figures here.
eg hundreds or less, thousands, tens of thousands, hundreds of thousands.

4. Is this to be included as part of the previous macro or a separate macro?

Drewgarry007 · Oct 6, 2011

Hi,

1) My answer would be option B) I would like the results to be on sheet1.
- I would like the results at the last occurence of each rate. And the check results to be in column P. If the splits dont add up then highlight the row with a color

2) I use 2007 version of excell at work.

3) The number of rows would only be in the hundreds

4) Yes this is the next step in a macro with many steps... basically I am sorting out a list of FX trades against another list of FX trades. The bulk trades are copy/pasted into sheet2 and the split (break down) trades from another source are copy/pasted in sheet1.
So far I have written the macro to format the trades in sheet1 as these are ones I want to use eventually.
Your prevoius code allowed me to then remove the trades in sheet1 that I dont need, now I just want to check that the splits in sheet1 add up to the bulk trades in sheet2.

There are one or two other checks I would like to run but I dont want to ask too much!! you have been very helpful to this novice vba user!!

Peter_SSs · Oct 6, 2011

Try this (after the other code has been run to eliminate the unwanted rows). I have assumed that the data on Sheet1 is sorted by column I (Customer Rate). If not, could it be sorted that way?

 Sub CheckTotals() Dim lr1 As Long, lr2 As Long Dim frmla As String Const frmlabase As String = _ "=IF(I2=I3,"""",IF(SUMIF(I$2:I2,I2,F$2:F2)" _ & "=SUMIFS(Sheet2!K$2:K$#,Sheet2!Q$2:Q$#,I2,Sheet2!I$2:I$#,E2)" _ & "+SUMIFS(Sheet2!O$2:O$#,Sheet2!Q$2:Q$#,I2,Sheet2!M$2:M$#,E2)" _ & ",""OK"",""ERROR""))" With Sheets("Sheet2") lr2 = .Range("Q" & .Rows.Count).End(xlUp).Row End With frmla = Replace(frmlabase, "#", lr2, 1, -1, 1) With Sheets("Sheet1") lr1 = .Range("I" & .Rows.Count).End(xlUp).Row Application.ScreenUpdating = False Range("P1").Value = "Check Totals" With .Range("P2:P" & lr1) .Formula = frmla .Value = .Value End With Application.ScreenUpdating = True End With End Sub 

If this does what you want, then rather than combine all your checks into one big procedure, I suggest that you create one 'Master' procedure that runs each of the individual checks. Something like this:

Code:

Sub MasterControl()
    CheckRatesList
    CheckTotals
End Sub

Peter_SSs · Oct 6, 2011

Actually, if columns Q:R are available to use as helper columns, though the code is a bit longer, the formulas could be simplified with this code.

 Sub CheckTotals() Dim lr1 As Long, lr2 As Long Dim frmlaP As String, frmlaQ As String, frmlaR As String Const frmlabaseP As String = "=IF(I2=I3,"""",IF(SUMIF(I$2:I2,I2,F$2:F2)" _ & "=INDEX(Sheet2!K$2:O$#,Q2,R2),""OK"",""ERROR""))" Const frmlabaseQ As String = "=MATCH(I2,Sheet2!Q$2:Q$#,0)" Const frmlabaseR As String = "=MATCH(E2,INDEX(Sheet2!I$2:M$#,Q2,0))" With Sheets("Sheet2") lr2 = .Range("Q" & .Rows.Count).End(xlUp).Row End With frmlaP = Replace(frmlabaseP, "#", lr2, 1, -1, 1) frmlaQ = Replace(frmlabaseQ, "#", lr2, 1, -1, 1) frmlaR = Replace(frmlabaseR, "#", lr2, 1, -1, 1) With Sheets("Sheet1") lr1 = .Range("I" & .Rows.Count).End(xlUp).Row Application.ScreenUpdating = False Range("P1").Value = "Check Totals" With .Range("P2:P" & lr1) .Offset(, 1).Formula = frmlaQ .Offset(, 2).Formula = frmlaR .Formula = frmlaP .Value = .Value .Offset(, 1).Resize(, 2).ClearContents End With Application.ScreenUpdating = True End With End Sub

Drewgarry007 · Oct 7, 2011

This code only works if the Currency Amts are in Sheet2 (col O). If the total amount we want to check against are in (col K) then your code brings back an error..

The problem is that the total notionals I want to check against could be in either Currency2Amount (col O) or Currency1Amount (col K) see below:

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=387 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 84pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=112>Currency1Amount</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=11>;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=66>Currency2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=11>;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=112>Currency2Amount</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=11>;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>DealtRate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 height=17 align=right x:num="-3430000">-3,430,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">USD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="2561548.01">2,561,548.01</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="0.746807">0.746807</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num="-512589.77">-512,589.77</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">CAD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 align=right x:num="540000">540,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.053474">1.053474</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num="21006256.460000001">21,006,256.46</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">CAD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 align=right x:num="-22200000">-22,200,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.0568280000000001">1.056828</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num="-20972758.09">-20,972,758.09</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">CAD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 align=right x:num="22200000">22,200,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.058516">1.058516</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num="-4211529.98">-4,211,529.98</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">CAD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 align=right x:num="4470000">4,470,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.061372">1.061372</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num="-4238158.12">-4,238,158.12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">CAD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 align=right x:num="4500000">4,500,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.061782">1.061782</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 height=17 align=right x:num="5330000">5,330,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">USD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-7024001.9199999999">-7,024,001.92</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.3178240000000001">1.317824</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num="-4620000">-4,620,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">USD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="6105145.2000000002">6,105,145.20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">;</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.3214600000000001">1.32146</TD></TR></TBODY></TABLE>

Is this getting too complicated?

Filtering rates from rows

Drewgarry007

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Drewgarry007

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Drewgarry007

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Drewgarry007

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Drewgarry007

Board Regular

Similar threads

Forum statistics

Share this page

Filtering rates from rows

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock