VBA Help to compare data and return result if match found

[Emmily]

Hi

I have a work task which I want to automate via VBA. I want the code to first clear the data in Col K "ControlSheet" and then do a lookup by looking at Col I and J in Sheet "ControlSheet" and compare this to the concatenated data in Col D in Sheet "HiportData" if match found then return Holding amount in Col K based on the holding shown in Col I "HiportData". If match not found then return 0 rather than #N/A. Please note there will be trailing spaces in the data, so the code will need to remove these first.

Real data will be around 1500 rows.

Excel Workbook
	I	J	K
1	Security Code	Pfolio Code	Hiport Holding
2	EQI0005	HRISII	15636.36
3	AMPSCF	CSFAUP
4	MFAFS004	N4S15X
5	MFAFS005	WSRAPP
ControlSheet

Excel Workbook
	D	I
1	UUTID	Holding
2	MFAFS004N4S15X	1,000.00
3	EQI0005HRISII	15,636.36
4	MFAFS005WSRAPP	98,784.00
HiportData

Thanks in advance

 

[hiker95]

Emmily,

Sample worksheets:

Excel 2007
	D	E
1	UUTID	Holding
2	MFAFS004N4S15X	1,000.00
3	EQI0005HRISII	15,636.36
4	MFAFS005WSRAPP	98,784.00
5
HiportData

Excel 2007
	I	J	K
1	Security Code	Pfolio Code	Hiport Holding
2	EQI0005	HRISII
3	AMPSCF	CSFAUP
4	MFAFS004	N4S15X
5	MFAFS005	WSRAPP
6
ControlSheet

After the macro in worksheet ControlSheet:

Excel 2007
	I	J	K
1	Security Code	Pfolio Code	Hiport Holding
2	EQI0005	HRISII	15,636.36
3	AMPSCF	CSFAUP
4	MFAFS004	N4S15X	1,000.00
5	MFAFS005	WSRAPP	98,784.00
6
ControlSheet

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub GetHiportHolding()
' hiker95, 11/14/2014, ME82838
Dim wc As Worksheet, wh As Worksheet
Dim c As Range, u As Range, h As String, lr As Long
Application.ScreenUpdating = False
Set wc = Sheets("ControlSheet")
Set wh = Sheets("HiportData")
With wc
  lr = .Cells(Rows.Count, 11).End(xlUp).Row
  If lr > 1 Then .Range("K2:K" & lr).ClearContents
  For Each c In .Range("I2", .Range("I" & Rows.Count).End(xlUp))
    h = c.Value & c.Offset(, 1).Value
    Set u = wh.Columns(4).Find(h, LookAt:=xlWhole)
    If Not u Is Nothing Then
      With c.Offset(, 2)
        .Value = wh.Cells(u.Row, 5).Value
        .NumberFormat = "#,##0.00"
      End With
      Set u = Nothing
    End If
  Next c
End With
wc.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetHiportHolding macro.

 

[Emmily]

Thanks Hiker for your time on this macro, i tested the code but nothing happened, no records were returned, this could be because if you look at the holdings in sheet "HiportData" they are in Col I not Col E as per your screenshot

 

[Emmily]

Ok i changed .Value = wh.Cells(u.Row, 9)

The code does not return holdings where the data has trailing spaces

Also when the data is not matched the cells does not reflect "0" it just reflects a blank cell

 

[Peter_SSs]

The code does not return holdings where the data has trailing spaces

Which data has trailing spaces?

 

[Emmily]

Which data has trailing spaces?

Hi Peter, data in Control sheet has spaces

 

[Peter_SSs]

Try this on a copy of your workbook.

Sub HiportHolding()
  Dim Adr As String
  
  With Sheets("ControlSheet")
    Adr = .Range("K2:K" & .Range("J" & .Rows.Count).End(xlUp).Row).Address
    .Range(Adr).Value = Evaluate("if(row(),sumif(HiportData!D:D,trim(" & Replace(Adr, "K", "I") & ")&trim(" & Replace(Adr, "K", "J") & "),HiportData!I:I),"""")")
  End With
End Sub

 

[hiker95]

Emmily,

Thanks Hiker for your time on this macro, i tested the code but nothing happened, no records were returned, this could be because if you look at the holdings in sheet "HiportData" they are in Col I not Col E as per your screenshot

OK, I have that fixed.

The code does not return holdings where the data has trailing spaces

Also when the data is not matched the cells does not reflect "0" it just reflects a blank cell

Can we have some screenshots that reflect the above? You could color those cells, and, in a cell to the right display the text with the space characters within " " marks.

 

[Peter_SSs]

Can we have some screenshots that reflect the above?

Are you looking for something too complex? Maybe I'm wrong but the original post stated

If match not found then return 0 rather than #N/A.

As far as I can see it just means in your code if u is nothing then put a 0 in the offset cell rather than just skipping to the next row & leaving the offset cell blank.

 

[hiker95]

Peter_SSs,

Got it now - thanks.

Now, if Emmily can show us where the space characters are I can update my macro.