Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask NewITZone Your Own Question

NewITZone, Computer Enthusiast

Category: Microsoft Office

Satisfied Customers: 90

Experience: I have been using Office Pro for 10 years and implemented applications for business use.

53509759

Type Your Microsoft Office Question Here...

NewITZone is online now

Comparing 2 identically formated worksheets to find

Customer Question

Comparing 2 identically formated worksheets to find differences and produce values? I would like SHEET1 A4 to produce the following values: (to be copied to 50,000+ rows) "NEW ITEM" - IF ACCT# : B4 and ITEM# : D4 do NOT match SHEET2 (same colums any row) BLANK - IF ACCT# : B4 and ITEM# : D4 do match SHEET2 (same colums any row) "NEW ACCT" - IF Sheet1 ACCT# : B4 does NOT match SHEET2 ACCT# : column A (any row) "NEW ITEM" - IF ACCT# : B4 and ITEM# : D4 do match SHEET2 (same colums any row) AND Sheet1 TY 90 DAYS : E4 =BLANK or zero AND Sheet1 TY 90 DAYS : E4 = greater than zero

Submitted: 1 year ago.

Category: Microsoft Office

Share this conversation

Customer:replied 1 year ago.

I have attached a small screen shot of the worksheets I am working with. Ultimately I am trying to produce one of the values below in column A of Sheet1 base on the instruction above: "NEW ACCOUNT" (base on 2 different criteria) or "NEW ITEM" or BLANKThank You

Need formulas to compare 2 identically formatted worksheets to find differences and produce values? I would like SHEET1 A4 to produce the following values: (to be copied to 50,000+ rows)"NEW ITEM" - IF ACCT# : B4 and ITEM# : D4 do NOT match SHEET2 (same colums any row) BLANK - IF ACCT# : B4 and ITEM# : D4 do match SHEET2 (same colums any row) "NEW ACCT" - IF Sheet1 ACCT# : B4 does NOT match SHEET2 ACCT# : column A (any row) "NEW ITEM" - IF ACCT# : B4 and ITEM# : D4 do match SHEET2 (same colums any row) AND Sheet1 TY 90 DAYS : E4 =BLANK or zero AND Sheet1 TY 90 DAYS : E4 = greater than zero

Customer:replied 1 year ago.

CORRECTION:I have attached a small screen shot of the worksheets I am working with. Ultimately I am trying to produce one of the values below in column A of Sheet1 base on the instruction above: "NEW ACCOUNT" or "NEW ITEM" (base on 2 different criteria) or BLANKThank You

Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

I am very sorry about your issues. Can you send me a sample or dummy Excel file with sample or dummy data so that I can actually work on your requirement and send it back to you for checking?

You can attach the sample Excel file when you reply using the paper clip icon.

Please let me know by replying to me here so that I can help you further.

I would like SHEET1 A4 to produce the following values: (to be copied to 50,000+ rows) "NEW ITEM" - IF ACCT# : B4 and ITEM# : D4 do NOT match SHEET2 (same colums any row) BLANK - IF ACCT# : B4 and ITEM# : D4 do match SHEET2 (same colums any row) "NEW ACCT" - IF Sheet1 ACCT# : B4 does NOT match SHEET2 ACCT# : column A (any row) "NEW ITEM" - IF ACCT# : B4 and ITEM# : D4 do match SHEET2 (same colums any row) AND Sheet1 TY 90 DAYS : E4 =BLANK or zero AND Sheet1 TY 90 DAYS : E4 = greater than zero

====

Can you clarify this requirement further? Or can you do an example using the sample file you gave me? For instance, in Sheet 1, A3, what is your expected result?

Sheet1, A3 result "NEW ITEM" because B3 and H3 match up to the same columns (row position will vary) on Sheet2 AND Sheet1, N3 is greater than zero AND Sheet2, M3 is equal to or less than zero.

I have built a formula for you using the conditions you confirmed to me. In the sample file you have me, ONLY Sheet1 A3 resulted to New ITEM. The rest (Sheet 1 A4 to A25) are showing NEW ACCOUNT.

Please check the sample file you gave me with the sample data. Are my results correct?

NO. I sent a second dummy file over with proper results filed in. Maybe I can clarify using column names:1) If Sheet1, ACCT# ***** Sheet2, ACCT# ***** Sheet1, ITEM# ***** not match Sheet2, ITEM# = result "NEW ITEM" 2) If Sheet1, ACCT# ***** ITEM# ***** Sheet2, ACCT# ***** ITEM# ***** Sheet1, TY 90 DAYS =1 or greater and Sheet2, TY 90 DAYS =0 or less = result "NEW ITEM" 3) If Sheet1, ACCT# ***** Sheet2, ACCT# (coulm A) find no match = result "NEW ACCOUNT" 4) If Sheet1, ACCT# ***** ITEM# ***** Sheet2, ACCT# ***** ITEM# ***** Sheet2, TY 90 DAYS, is great than or equal to Sheet1, TY 90 DAYS = result "BLANK"

One more thing since the conditions are very complicated. Are you comparing or matching values per exact row? Or are you tying to match the value in Sheet1 to ANY match in the entire column in Sheet 2? I am asking because Sheet 1 has 23 rows while Sheet 2 only has 18 rows. Please confirm if the values in Sheet 1 are to be compared and matched to the entire column in Sheet 2.

For instance, for Sheet 1 A21, how did you determine it will be blank when Sheet 2 data ends in row 20 only?

Sorry. You are correct a23 should have been new item. Not blank. Actually A21 A21 A23 should be new item. Because they do not match both columns in sheet2. You are getting it. Thank you.

Customer:replied 1 year ago.

Exactly. If there are more items on sheet one then sheet to that means the extra items would have to be either new items or new accounts. The reason is because the Account number and item number columns do not match. So result new item

Thank you for that confirmation that the matching is done per row and not the entire column. Please give me a moment to build the required formula for this.

sorry. you may have misunderstood. the matching is done per row but the row position could change any time the spread sheet is updated. so it is looking for a row match anywhere in the column. hope that helps.

Customer:replied 1 year ago.

If I was to rearrange the rows the result values for each row will still be the same. The column positions will always be the same. In the dummy file it just so happened that the Rows lined up. But this is not always the case. I will be dealing with over 50,000 rows.

Thank you for writing back. So are you saying that for instance, Sheet1 B3, it is going to locate a match for the entire column A in Sheet 2 like A2:A50000 ?

Thank you for writing back and I am very sorry about the late reply. I am still working on this and will finish the required formula in a while. This has become complex when you confirmed that the data in Sheet 1 is to be compared to the entire corresponding column in Sheet 2.

What happen? Very disappointed in your service so far. I was able to solve the problem, no thanks to Just Answers! I will be demanding my money back! =IF(ISERROR(VLOOKUP(D3&J3,Sheet2!R:R,1,0)),"NEW SKU"," ") =IF(ISERROR(VLOOKUP(D3&J3,Sheet2!R:R,1,0))," ",IF(AND(P3>0,Sheet2!N3=" "),"NEWSKU90"," ")) =IF(ISNA(VLOOKUP(D3,Sheet2!B:B,1,FALSE)),"NEW ACCT","")

Thank you for writing back and I am glad you fixed the problem. I checked your formula and it will not work according to what you have described to me. The scenario you described based on the sample file you gave me was far too complex compared to the formula you said that worked. My formula accommodates dynamic data and uses match function since VLOOKUP will not work in the scenario you described to me. I am almost done with the complex logic and the final formula but I believe you no longer need it.

Yes, we guarantee our service so you can always get your money back. Please use the link below for more information about refunding: