Ask Question Forum:
Model Library:2025-02-08:A.I. model is online auto reply
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by sfletcher1959
at 2024-08-01 08:33:47
ModifyPoint:500 Replies:4 POST_ID:828655USER_ID:11538
Topic:
Microsoft Applications;Microsoft Office Suite;Microsoft Excel Spreadsheet Software
I am going to try and keep this simple. The two spreadsheets that I need to compare have 20000 entries on one and 40000 entries on the other. Ultimately I need to identify what entries are on the first sheet that are not on the second sheet and line them up on the final sheet. So I have included a few pictures of a simple sample.
The first spreadsheet has four fields that need to be lined up with the same data in the second spreadsheet and output to a third sheet
I have attached a picture of the first sheet
I have attached a picture of the second sheet
I have attached a picture of what the third sheet needs to look like.
Is there a formula or function that will output what I am trying to accomplish.
I am not a power Excel user so be gentle! :)
The first spreadsheet has four fields that need to be lined up with the same data in the second spreadsheet and output to a third sheet
I have attached a picture of the first sheet
I have attached a picture of the second sheet
I have attached a picture of what the third sheet needs to look like.
Is there a formula or function that will output what I am trying to accomplish.
I am not a power Excel user so be gentle! :)
Attachment:Sheet-1.pngSheet-2.pngSheet-3.png
Expert: Ejgil Hedegaard replied at 2024-09-06 15:28:42
I see it is a long time ago you posted your comment, did not see it then due to vacation.
Are you still working on this, and having problem.
Is it possible to upload the file, (or is it too big), then I can fix it for you.
I would go for the second suggestion, with a third sheet and a match column for sheet 1 and one for sheet 2.
Are you still working on this, and having problem.
Is it possible to upload the file, (or is it too big), then I can fix it for you.
I would go for the second suggestion, with a third sheet and a match column for sheet 1 and one for sheet 2.
Author: sfletcher1959 replied at 2024-08-08 13:54:29
Okay still a little lost. Have tried this several times, but seem to be in a loop. Is there a way to locate Excel Guru's that I could work with in real time to figure this out.
Accepted Solution
Expert: Ejgil Hedegaard replied at 2024-08-01 14:29:33
500 points EXCELLENT
Assuming that the sheet with 40.000 entries have all that exist on the sheet with 20.000 + 20.000 more.
On the first sheet (Sheet1 with 20.000 entries) in column E make a text combining the 4 columns A to D.
In cell E2 insert the formula =A2&B2&C2&D2
Copy down for all records.
On the other sheet (Sheet2) in E2 you can check if it exist on the first sheet
=IF(ISERROR(MATCH(A2&B2&C2&D2,Sheet1!E:E,0)),"No match","Match")
Copy down. It takes some time with so many records.
If the record can be found on Sheet1, then "Match", else "No match".
If you then want something like in picture 3, you can make formulas in columns F to I
F2: =IF($E2="Match";A2;"")
Copy down to all entries, and colums F to I.
But since they will be identical to columns A to D if there is a match, I don't see the point.
If both sheets have entries that don't exist on the other, I would make another approch.
Copy all entries from both sheets into a third sheet, after each other, so you have 60.000 entries.
Then use the remove duplicate function on the Data tab.
In column E insert the formula to check for match on Sheet1, and in column F a similar formula but with reference to Sheet2.
Then entries with "Match" in both column E and F exist on both sheets.
When done you could change all the results to values, to avoid waiting for recalculation when you change something.
Match for 40 to 60.000 entries looking in as many will take some time.
Mark columns E and F, Copy and Paste Special - Values.
On the first sheet (Sheet1 with 20.000 entries) in column E make a text combining the 4 columns A to D.
In cell E2 insert the formula =A2&B2&C2&D2
Copy down for all records.
On the other sheet (Sheet2) in E2 you can check if it exist on the first sheet
=IF(ISERROR(MATCH(A2&B2&C2&D2,Sheet1!E:E,0)),"No match","Match")
Copy down. It takes some time with so many records.
If the record can be found on Sheet1, then "Match", else "No match".
If you then want something like in picture 3, you can make formulas in columns F to I
F2: =IF($E2="Match";A2;"")
Copy down to all entries, and colums F to I.
But since they will be identical to columns A to D if there is a match, I don't see the point.
If both sheets have entries that don't exist on the other, I would make another approch.
Copy all entries from both sheets into a third sheet, after each other, so you have 60.000 entries.
Then use the remove duplicate function on the Data tab.
In column E insert the formula to check for match on Sheet1, and in column F a similar formula but with reference to Sheet2.
Then entries with "Match" in both column E and F exist on both sheets.
When done you could change all the results to values, to avoid waiting for recalculation when you change something.
Match for 40 to 60.000 entries looking in as many will take some time.
Mark columns E and F, Copy and Paste Special - Values.
Expert: duncanb7 replied at 2024-08-01 08:35:39
Do you know Excel VBA programming ?
Please change the tag also for including VBA and VBA programming so that
other Excel VBA expert can help you in fast way
Please change the tag also for including VBA and VBA programming so that
other Excel VBA expert can help you in fast way
