Matching Data

[DEEARO]

I have an list of names (sheet 2) that should be in the Sheet 1 Column B.

I want to create a way so I know which names from sheet 1 need to be removed and which names need to be added.

Any help will be well appreciated

[GeoffW283]

You can get your desired end result simply by copying the sheet2 list of names to sheet1, but I'm guessing you want to see explicitly who is added and who is deleted.

Note that you state in Sheet1!B1 that names should be copied exactly as on [Sheet2] yet you have "Bains Raj" on Sheet1 and "Raj Bains" on sheet 2. I corrected that.

On Sheet1!C2 I added:

=IF(AND(ISERROR(MATCH([@CLAIMANT], Sheet2!A:A, 0)), [@CLAIMANT]<>""), "REMOVE", "")

This column contains "REMOVE" as appropriate.
Note: I shortened the title in your table to "Claimant" so that the formula doesn't look quite as horrendous.

On Sheet2!B2:

=IF(ISERROR(MATCH(A2, Caseload[@CLAIMANT], 0)), "ADD", "")

This column contains "ADD" as appropriate.

A modified version of your workbook implementing the above is attached.

Hope this helps. Let us know.

[DEEARO]

hi thank you very much for the help. however i would like results to show in separate tables like shown in sheet 1 - NAMES TO BE REMOVED and NAMES TO BE ADDED with the actual names

[maniacb]

I have a solution for you. I think that a "misspelling" mentioned above was actually on purpose, as you probably want to capture the name whether the surname or the first name is on the list. I did not include the naming solution, but made it so that you achieve your requested results. Let me know if you have questions.

Sub Pass1()
   Dim Ary As Variant
   Dim i As Long
   With Sheets("Sheet1")
      Ary = .Range("b2", .Range("b" & Rows.Count).End(xlUp)).Value2
   End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(Ary)
            .Item(Ary(i, 1)) = Empty
        Next i
        With Sheets("Sheet2")
            Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
        End With
        For i = 2 To UBound(Ary)
            If .Exists(Ary(i, 1)) Then .Remove Ary(i, 1)
        Next i
      Ary = .keys
    End With
    Dim Dest As Range
    Set Dest = Range("d3")
    Set Dest = Dest.Resize(UBound(Ary)+1, 1)
    Dest.Value = Application.Transpose(Ary)
  
    pass2
    
End Sub
Sub pass2()
   Dim Ary As Variant
   Dim i As Long
   With Sheets("Sheet2")
      Ary = .Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value2
   End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(Ary)
            .Item(Ary(i, 1)) = Empty
        Next i
        With Sheets("Sheet1")
            Ary = .Range("b2", .Range("b" & Rows.Count).End(xlUp)).Value2
        End With
        For i = 1 To UBound(Ary)
            If .Exists(Ary(i, 1)) Then .Remove Ary(i, 1)
        Next i
      Ary = .keys
    End With
    Dim Dest As Range
    Set Dest = Range("f3")
    Set Dest = Dest.Resize(UBound(Ary) + 1, 1)
    Dest.Value = Application.Transpose(Ary)
    
   
End Sub

The file is too large to upload. I will try to shrink and upload asap.

[GeoffW283]

Apologies for the misunderstanding. Attached is a revised workbook that I think now does what you want.

You now have a choice of a VBA based solution from maniacb or this formula based solution. Hopefully at least one of them work for you.

@maniacb: As cell B1 on the OP's workbook says "CLAIMANT NAME
(copy the name exactly as on the build)" I assumed that "Bains Raj" would not be considered to be equivalent to "Raj Bains". Let's see what the OP says!

In the attached workbook:
Columns H is a "helper" column for the "names to be removed". It lists the required names but potentially with blanks in between. In H2 copied down to H20 or in your final application copy as far as the length of your list in Sheet1!B:

=IF(B2="", "", IF(ISERROR(MATCH(B2, Sheet2!A:A, 0)), B2, ""))

Names to be removed are calculated in column-D. The formula simply removes any blanks present in column-H. In D2 copied down to D20:

=IFERROR(INDEX($H$2:$H$20, AGGREGATE(15,6,IF(NOT($H$2:$H$20=""), ROW($A$2:$A$20)-1,""), ROW(A1))),"")

Change "20" in this formula as required.

"Names to be added" are calculated in column-F with the assistance of helper column-I. The formulas are similar to the "names to be removed" formulas.

The attached workbook implements the above.

[DEEARO]

hi GeoffW283 and GeoffW283, thank you very much for the solutions. i prefer the formula based solution. i have face slight issue with he formula. it there is a extra space then this causes errors

[GeoffW283]

Thanks for the feedback!

First, something you have done has corrupted the formulas in the following cells: D4, F4, D8, F8. What alerted me was the little green triangle in the top left corner of these cells that indicates a possible anomalous formula. I fixed this by selecting D2:F2 and copy/paste-formula down to row 20. That explains why MIKE JOHN did not appear in the "Names to be added list" which I believe was a problem. I have fixed this in the attached mod to your workbook. In general there should be no need for you to change anything in any of columns D thru J.

Now onto your problem. You say that an extra space at the end of "JOHN" in cell sheet1!B8 "causes errors". At present as there is no "JOHN " on sheet2 then "JOHN " has been placed on the "Names to be removed" list which seemed logical. Do you want me to ignore space characters at the end of names in sheet1-col-B? How about spaces at the end of names on sheet2-col-A?

Let me know.

Thanks

Geoff

[DEEARO]

thank you very much for fixing the formula. Spaces can be in both sheets (both list of names), it all depends no how people type and when coping the information for our database can can cause spaces.

[GeoffW283]

Spaces can be in both sheets (both list of names), it all depends no how people type and when coping the information for our database can can cause spaces.

If Ali's suggested approach is possible then go with that.

If that's not possible then you could use the trim() function to clean up your source data. This function removes leading and trailing spaces.

If neither of the above is possible then I have modified the helper column formulas to make copious use of the trim() function. This is the least desirable approach as it adds complexity and in addition now both helper column formulas need to be entered as array formulas.
H2 copied down:

=IF(TRIM(B2)="", "", IF(ISERROR(MATCH(TRIM(B2), TRIM(Sheet2!$A$2:$A$20), 0)), TRIM(B2), ""))

J2 copied down:

=IF(TRIM(Sheet2!A2)="", "", IF(ISERROR(MATCH(TRIM(Sheet2!A2), TRIM($B$2:$B$20), 0)), TRIM(Sheet2!A2), ""))

Bothe of these formulas need to be entered as array formulas with CTRL-SHIFT-ENTER.

The attached workbook implements these changes.

Let us know which, if any, of these approaches works for you.

[AliGW]

Why not mitigate the problem? Create a data validation list of names for them to choose from.

[DEEARO]

thank you very much GeoffW283 that is prefect for what I wanted. I have small problem I cannot solve. I have changed the layout and now I am getting an error (FALSE)message (SEE sheet named RESULTS) and not sure how to resolve it.

[GeoffW283]

Somehow during your layout change some of the formulas got scrambled. I have fixed this in the attached revision (revA) of your file. I have also copied the formulas down to row 1000 which I think is part of what you were trying to do. I tested these changes against your caseload3.xlsx data as well as the data in your prior workbook and I believe both give the expected results.

Let me know if this works for you.

Geoff

[DEEARO]

Thank you very much , amazing

[DEEARO]

sorry again I have future issues.

whilst copy and pasting the data from the actual source I get data which is not required. I can filter the data however this not reflecting well with the results as rows go missing and filter information still appearing. Not sure what is the best solution, I was thinking maybe having the data on the another tab and copy and pasting the information over but is there an easier way?

[GeoffW283]

First an observation (and I also saw this on a previous iteration of your workbook): The formulas in C2:I2 are not being copied down to subsequent rows correctly. One example: in cell C3 towards the end of the formula there is a reference to ROW(A2), but in the next row, cell C4 references ROW(A4) - it should be ROW(A3). There's a number of similar problems. I'm not sure what you are doing to cause this but it is easily repaired: First, clear all filters then select C2:I2 and copy down as many rows as you need.

That doesn't solve your issue which I think is that you don't want to see "intensive" or "intensive supporting" (and maybe others) appearing in the "names to be removed column. One thought - would it be possible to filter out these values right at the data source - before they are imported to Excel? Are there other entries to be filtered, if so how do I distinguish then from real names?

While you contemplate the above, let me think if there's anything I can do formula-wise.

[DEEARO]

Thank you very for noticing the problem in the rows which I know have fixed.



When coping the information for our internal website (I have tired power query - to get data directly but the site blocks it - it is password protected and we not given the password - it automatically signs us in)

I filter the data first on the site but when I copy and paste the data in excel it still comes with information not required (mainly intensive and intensive support).

At the moment I copying the information into another tab ("filter"), filtering the data and then coping and pasting it result tab. Maybe there could be formula which automatically gets the filtered information from "filter tab" Or maybe you have another solution.

[GeoffW283]

Maybe there could be formula which automatically gets the filtered information from "filter tab".

I'll go with your idea. On the RESULTS worksheet enter the following formula in cell A2 and copy down as far as is needed:

=IFERROR(T(INDEX(filter!$A$1:$A$1000,  SMALL(IF((filter!$A$2:$A$1000="intensive")+(filter!$A$2:$A$1000="intensive supporting"), "", ROW($C$2:$C$1000)), ROW()-ROW($C$1)))), "")

Note that this is an array formula and needs to be entered via CTRL-SHIFT-ENTER.

The formula filters out "intensive" and "intensive supporting". It could readily be extended to add a few more items, but too many would get unwieldy.

I have attached an update to your workbook with the above change made.

Hopefully this helps. Let us know how you get on with it.

[DEEARO]

Hey GeoffW283 thank you very much. It works but I found one further issue, I am getting blank rows in my results. I believe this is because I get blanks when I copy and paste the information for the original source

[GeoffW283]

OK, here's a small change to the formula to filter out entirely blank lines. But first check whether the Filter tab lines are entirely blank or whether there's a "space" character as well. If the latter then change filter!$A$2:$A$15="" to filter!$A$2:$A$15=" ". If there's a varying number space characters then let me know.

I've also reformatted the formula to hopefully make it a bit more obvious how to modify the formula to filter out additional unneeded rows if required.

On the RESULTS worksheet enter the following formula in cell A2 and copy down as far as is needed:

=IFERROR(T(INDEX(filter!$A$1:$A$1000,  SMALL(IF(

(filter!$A$2:$A$1000="intensive")+

(filter!$A$2:$A$1000="intensive supporting")+

(filter!$A$2:$A$1000=""),

 "", ROW($C$2:$C$1000)), ROW()-ROW($C$1)))), "")

This is still an array formula and needs to be entered via CTRL-SHIFT-ENTER.

Let me know if this works for you.

Geoff

[maniacb]

Deearo, I went ahead and completed the VBA solution to your original request. Geoff formulas are impressive and I am learning a lot from them. Here is the VBA solution for you.

Option Explicit
Sub filterData()
Dim Rng As Range
Dim last As Long
Dim sht, shtR As String
Dim c As Range

'specify sheet name in which the data is stored
sht = "filter"
shtR = "RESULTS"
last = Sheets(sht).Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Sheets(sht).Range("A1:A" & last)
'rng = Sheets(sht).Range("A1")
For Each c In Rng
If c = "intensive" Or c = "intensive supporting" Then
    c = ""
    Else
    c = c
    
End If
Next c
'Remove blank cells
Dim counter As Integer, i As Integer
counter = 0
For i = 1 To last
    If Cells(i, 1).Value <> "" Then
        Cells(counter + 1, 26).Value = Cells(i, 1).Value 'copy to column Z or 26
        counter = counter + 1
    End If
Next i
With ThisWorkbook.Sheets(sht)
    Range("A1:A" & last).Value = ""
    Range("A1:A" & last).Value = Range("z1:z" & last).Value
    Sheets(shtR).Range("A1:A" & last).Value = Range("z1:z" & last).Value
    Range("z1:z" & last) = "" 'delete column z after moving names to RESULTS
End With
    Sheets("RESULTS").Activate 'Move focus to Results sheet
    Sheets("RESULTS").Range("a1").Select
    Call Pass1
End Sub


Public Sub Pass1()
   Dim Ary As Variant
   Dim i As Long
   Dim Dest As Range
   
   With Sheets("RESULTS")
        Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
        Set Dest = Range("C2")
        .Activate
   End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(Ary)
            .Item(Ary(i, 1)) = Empty
        Next i
        With Sheets("CASELOAD")
            Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
        End With
        For i = 2 To UBound(Ary)
            If .Exists(Ary(i, 1)) Then .Remove Ary(i, 1)
        Next i
      Ary = .keys
    End With
    Sheets("RESULTS").Activate
    Set Dest = Dest.Resize(UBound(Ary) + 1, 1)
    Dest.Value = Application.Transpose(Ary)
    'MsgBox Join(Ary)
      
    Call pass2
    
End Sub
Public Sub pass2()
   Dim Ary As Variant
   Dim i As Long
   Dim Dest As Range
   
   With Sheets("CASELOAD")
        Ary = .Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value2
        Set Dest = Range("E2")
   End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(Ary)
            .Item(Ary(i, 1)) = Empty
        Next i
        With Sheets("RESULTS")
            .Activate
            Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
        End With
        For i = 1 To UBound(Ary)
            If .Exists(Ary(i, 1)) Then .Remove Ary(i, 1)
        Next i
      Ary = .keys
    End With


    Set Dest = Dest.Resize(UBound(Ary) + 1, 1)
    Dest.Value = Application.Transpose(Ary)
    'MsgBox Join(Ary)
   
End Sub

[DEEARO]

Geoff thank you so much for the help and it has helped me a lot.

[GeoffW283]

No problem. Glad to help. Thanks for the feedback

[DEEARO]

I have noticed I am getting a slight issue with the formula. In the CASELOAD sheet if I delete a row, then I get error messages in the Filter sheet column G. Any solution will be appreciated

[GeoffW283]

I think you mean that the error message, #REF, appears in the Results worksheet, not the Filter worksheet right?
The reason is that if you delete a line from the CASELOAD then the formula on the corresponding row in RESULTS is trying to reference a row that no longer exists.

The easiest approach would be to use "clear contents" from a row rather than deleting the row. Will this work for you?

Alternatively to fix the #REF errors after you have deleted rows - once are done with deleting rows in CASELOAD then select G2 and copy the formula down all the way.

It neither of these approaches works for you then let me know - there may be a formula based solution that avoids the #REF errors, although there will likely be a performance penalty.

[DEEARO]

I understand thank you .


is there a way I can copy the sheet into another file without the formatting of the formula changing (when I copy the page into another excel sheet it, it still stays linked to the original file). I need to add filter and result sheets into another 85 workbooks for different staff can have a copy. What is the easiest way, each workbook already as caseload sheet)

[GeoffW283]

Can it work the other way around? - Like this:

1. In master_caseload.xlsx on the CASSELOAD worksheet select A2:A100 (or down to wherever your data ends
2. Right-click > Clear contents
3. In the first of your 85 workbooks - I'll call it Workbook_01 - on it's CASELOAD worksheet, select A2:A100 (or down to wherever your data ends)
4. Paste to the CASSELOAD worksheet in the master_caseload workbook
5. Save as workbook_01.xlsx
6. Re-open master_caseload.xlsx then repeat the above steps for each of your 85 workbooks

Is this a one-off exercise or something that you need to repeat regularly?

If the latter then you probably want to think about automating this. That would be a separate problem though!

Hopefully I have understood the problem. Let me know if the above works for you.

[DEEARO]

Thank you for the solution, it is one time task. I was just hoping there was a quicker solution then copying and pasting.

I will like to go one step further to original solution: is there way to automate the names that need to be added and removed from the caseload sheet.?

your help will be apricated

[GeoffW283]

I'm not sure why you attached the file? (a) it seems to have removed the filter formulas from results!A:A and (b) results!A1 contains DEEPAK rather than a header like "Name". This results in DEEPAK erroneously appearing in results!E2 (names to be added).

"I was just hoping there was a quicker solution then copying and pasting."

In addition it may be a concern for you that if the formulas need tweaking in any way you would now have to manually make the change in 85 different places.

I think this would have to be a VBA solution that might be feasible if it was possible to collect all 85 workbooks together in one folder. If they are scattered across numerous user accounts then I don't know how this would work. A robust VBA solution would be stretching my VBA skills. If you want to pursue this then maybe maniacb who provided a VBA solution in post #21 might have ideas about how this might work. Alternatively you might want to open up a new thread for this specific problem in the VBA section of this forum - there are many people there with vastly better VBA skills than me!

"I will like to go one step further to original solution: is there way to automate the names that need to be added and removed from the caseload sheet?"

If I correctly understand what you mean then I have added Results!B:B that implements the adds and removes. Let me know if I have this right.

See the attached workbook.

Geoff

[maniacb]

I am trying to visualize how it is you have 85 files and want to automate the process. We can create code that is run from your personal.xls file that can be run for each file, or run an automation on a file directory that contains all said files, as per GEOFF's recommendation. Although I have been following along, can you provide me a recap of what you may want automated, if you still want to pursue this option?