merging selective date from two worksheets

[cornepiek]

hi all.

can someone help me.
i need to do 2 things with the same macro.

i have two worksheets, lets make it 'client list' and 'target usage'.

the 'client list' contains active usage data.
something like this

A | B | C
1| Bolesco | 6559 |
2| Casidra | 3261 |
3| Donford | 1755 |
4| Just Connect | 7559 |
5| Maxtel | 1220 |
6| Mobifin | 1692 |
7| Pinnacle | 4458 |


the 'target usage' sheet will look something like this:

A | B | C
1| Bolesco | 7000 |
2| Casidra | 3500 |
3| Donford | 1500 |
4| Just Connect | 8000 |
5| Maxtel | 1500 |
6| Mobifin | 1700 |
7| | |


as you can see, the order of column A differ. also, there will be clients in the 'client list' that is not in the 'target usage' list.


here is what i need to do:
first:
compare columns A in 'client list' with column A in 'target usage'. if match is found, copy the info from 'target usage' column B to 'client list' column C in the same row as the where the matching client name was found.

second:
if there is a client in 'client list' sheet that is not in the 'target usage' sheet, the client name must be marked with red, as well as cell 'C' for the relevant client.

so, from the two sheets above, this must be the result:


"Client List" worksheet
A | B | C
1| Bolesco | 6559 | 7000
2| Casidra | 3261 | 3500
3| Donford | 1755 | 1500
4| Just Connect | 7559 | 8000
5| Maxtel | 1220 | 1500
6| Mobifin | 1692 | 1700
7| Pinnacle | 4458 | (Cell Colour Red)


please help

 

[Andrew Poulsom]

You can do all that with a VLOOKUP formula and conditional formatting.

 

[cornepiek]

i need to do it in a macro as i am adding it to a excel template to autoload when excel opens.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG03May29
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Ray
[COLOR="Navy"]Dim[/COLOR] Sht [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("client list")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("A1"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("target usage")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A1"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    Ray = Array(Rng1, Rng2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] Sht = 0 To UBound(Ray)
         [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Ray(Sht)
               [COLOR="Navy"]If[/COLOR] Not .Exists(Trim(Dn.value)) [COLOR="Navy"]Then[/COLOR]
                    .Add Trim(Dn.value), Dn
                    Dn.Font.ColorIndex = 3
                     Dn.Offset(, 2).Interior.ColorIndex = 3
               [COLOR="Navy"]ElseIf[/COLOR] Sht = 1 [COLOR="Navy"]Then[/COLOR]
                  .Item(Trim(Dn.value)).Offset(, 2) = Dn.Offset(, 1)
                 .Item(Trim(Dn.value)).Font.ColorIndex = 1
               .Item(Trim(Dn.value)).Offset(, 2).Interior.ColorIndex = xlNone
               [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]Next[/COLOR] Sht
[COLOR="Navy"]End[/COLOR] With
MsgBox "Run!!"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hiker95]

cornepiek,


Sample worksheets:

Excel Workbook
	A	B	C
1	Bolesco	6559
2	Casidra	3261
3	Donford	1755
4	Just Connect	7559
5	Maxtel	1220
6	Mobifin	1692
7	Pinnacle	4458
8
client list

Excel Workbook
	A	B
1	Bolesco	7000
2	Casidra	3500
3	Donford	1500
4	Just Connect	8000
5	Maxtel	1500
6	Mobifin	1700
7
target usage

After the macro:

Excel Workbook
	A	B	C
1	Bolesco	6559	7000
2	Casidra	3261	3500
3	Donford	1755	1500
4	Just Connect	7559	8000
5	Maxtel	1220	1500
6	Mobifin	1692	1700
7	Pinnacle	4458
8
client list

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).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub UpdateClients()
' hiker95, 05/3/2011
' http://www.mrexcel.com/forum/showthread.php?t=547505
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("client list")
Set w2 = Worksheets("target usage")
For Each c In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w2.Columns(1), 0)
  On Error GoTo 0
  If FR <> 0 Then
    c.Offset(, 2).Value = w2.Cells(FR, 2).Value
  Else
    c.Font.ColorIndex = 3
    c.Offset(, 2).Interior.ColorIndex = 3
  End If
Next c
Application.ScreenUpdating = True
End Sub

Then run the UpdateClients macro.

 

[cornepiek]

thanks guys.

i tried both an both are working like a charm