How JustAnswer Works:

  • Ask an Expert
    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
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
A new question is answered every 9 seconds

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
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
Customer: replied 1 year ago.
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
Expert:  Jess M. replied 1 year ago.

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.

Best regards,
Jess

Customer: replied 1 year ago.
Actual file sample attached. I highlighted the relevant rows. Thank you
Expert:  Jess M. replied 1 year ago.

Thank you. I got the file and there are 2 sheets with highlighted columns: Account Number and Items columns.

Are you working on Column A to fill out "Return Value"?

Expert:  Jess M. replied 1 year ago.

This is the criteria you mentioned:

====

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?

Customer: replied 1 year ago.
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.
Expert:  Jess M. replied 1 year ago.

I am sorry but I cannot follow your described conditions.

In sheets 1, A3 = NEW ITEM because:

  1. Sheet1B3 = Sheet2A3 AND
  2. Sheet1H3 = Sheet2G3 AND
  3. Sheet1N3 > 0 AND
  4. Sheet2M3 =< 0.

Is that correct? But Sheet2M3 is blank.

Customer: replied 1 year ago.
Sheet2, M3 will be blank or less than zero. So if Sheet1, N3 is greater than zero ( or 1 )result "NEW ITEM"
Customer: replied 1 year ago.
a blank is equal to zero on this spreadsheet
Expert:  Jess M. replied 1 year ago.

Thank you for that information. And if ANY of these conditions are NOT met, A3 will show NEW ACCT instead of NEW ITEM. Is this correct?

Customer: replied 1 year ago.
NEW ACCOUNT" is quite simple. IF Sheet1, ACCT#,B3 does not match Sheet2, ACCT#, column A then result "NEW ACCOUNT"
Expert:  Jess M. replied 1 year ago.

Thank you for that confirmation. Please give me a moment to build the formula for you.

Expert:  Jess M. replied 1 year ago.

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?

Customer: replied 1 year ago.
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"
Expert:  Jess M. replied 1 year ago.

Ok. Please give me a moment to check the second dummy file

Expert:  Jess M. replied 1 year ago.

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?

Customer: replied 1 year ago.
It is matched to the entire row correct.
Customer: replied 1 year ago.
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
Customer: replied 1 year ago.
Or new account.
Expert:  Jess M. replied 1 year ago.

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.

Best regards,

Jess

Customer: replied 1 year ago.
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.
Expert:  Jess M. replied 1 year ago.

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 ?

Customer: replied 1 year ago.
that is correct
Expert:  Jess M. replied 1 year ago.

Ok. Thank you for the confirmation.

Customer: replied 1 year ago.
hello. anything yet?
Customer: replied 1 year ago.
I have not had a response all day. I see my credit card was charged. Please let me know what is going on!
Expert:  Jess M. replied 1 year ago.

Hi,

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.

I will keep you posted.

Best regards,

Jess

Customer: replied 1 year ago.
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","")
Expert:  Jess M. replied 1 year ago.

Hi,

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:

http://ww2.justanswer.com/help/how-can-i-request-refund

I am sorry if I was not able to help, or was unable to finish the requirement you need.

Thank you for using our service.

Best regards,

Jess

Related Microsoft Office Questions