Matching Data

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:

Formula:

Please Login or Register  to view this content.

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:

Formula:

Please Login or Register  to view this content.

This column contains "ADD" as appropriate.

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

Hope this helps. Let us know.

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.

Please Login or Register  to view this content.

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

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:

Formula:

Please Login or Register  to view this content.

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:

Formula:

Please Login or Register  to view this content.

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.

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

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

Originally Posted by DEEARO

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:

Formula:

Please Login or Register  to view this content.

J2 copied down:

Formula:

Please Login or Register  to view this content.

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.

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.

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

Originally Posted by DEEARO

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:

Formula:

Please Login or Register  to view this content.

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.

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:

Formula:

Please Login or Register  to view this content.

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

Let me know if this works for you.

Geoff

No problem. Glad to help. Thanks for the feedback

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.

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.

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