Worksheets

[visible2you]

Hi,

In an Excel file, there are two sheets:-

1. Original
2. ImportedData

I am not allowed to change entire contents of Sheet(1), unless to add a unique and new row from Sheet(2) or vice-versa.

I am allowed to comment on contents of Sheet(1) or Sheet (2) in Column J only on basis of Col A, B and D.

Following comments are applicable: -

Duplicate,
Old,
New,
Updated or
Attached

Lets Imagine that Column J of both worksheets do not exist.

Based on either worksheet, We have to update values in Column J of both worksheets through VBA.

Priority (in order) to update sheets:-

ImportedData {which is Sheets(2)}
Original {which is Sheets(1)

It would mean that we have to start filling J Col of ImportedData Sheet first.

following Definitions apply:

Duplicate: Those duplicate values in Col A, B and D of Sheets(2)
Old: Those values which are same in Col A, B and D of Sheets(1) and Sheets(2).
New: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1)
Updated: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1) and were updated in Sheets(1) using data from Sheets(2).
Attached: Those values in Col A, B and D of Sheets(1) which does not exist in Sheets(2) and were attached in Sheets(2) using data from Sheets(1).

we call same row as "updated" in Sheets(1) , while we call it as "New" in Sheets(2)?

 

[ClulessChris]

Sorry but I've missed the question here.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[visible2you]

How can I comment on contents of Sheet(1) or Sheet (2) in Column J only on basis of Col A, B and D through VBA?

 

[SkipVought]

What do each of these mean? You must be very specific with a posted example of values in the specified columns and the logic applied on a specific row. You requirements are AMBIGUOUS.

For instance, does this apply to ANY value or ALL values (OR vs AND)? Are there identical values in multiple columns, or is each column of a single type of data?

EXAMPLES of each case using sample tables, please.

Duplicate: Those duplicate values in Col A, B and D of Sheets(2)

Old: Those values which are same in Col A, B and D of Sheets(1) and Sheets(2).

New: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1)

Updated: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1) and were updated in Sheets(1) using data from Sheets(2).

Attached: Those values in Col A, B and D of Sheets(1) which does not exist in Sheets(2) and were attached in Sheets(2) using data from Sheets(1).


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

Hi,

I am sorry that I missed this condition: -
AND condition applicable in below definitions: -

Duplicate: Those duplicate values in Col A, B and D of Sheets(2)
Old: Those values which are same in Col A, B and D of Sheets(1) and Sheets(2).
New: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1)
Updated: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1) and were updated in Sheets(1) using data from Sheets(2).
Attached: Those values in Col A, B and D of Sheets(1) which does not exist in Sheets(2) and were attached in Sheets(2) using data from Sheets(1).

 

[SkipVought]

EXAMPLES PLEASE!

DATA in TABLES, PLEASE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

Hi,

I request to please refer attachment.

http://www.mediafire.com/?vxg7mqnxlbekh53

 

[SkipVought]

Many of us have security restriction at our places of employment, so I cannot download your file.

If you want help, please post some sample data that illustrates each of these conditions.

Simply showing a table without a specific explanation of what each set of conditions & data relates to each expected result, is still inadequate.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

Number Customer Name Date sold Product sold Product Feedback Date returned Defective Product Date Repaired Product Error Number Comments
111111 ABC 1 03-09-2011 Apple 1 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111112 ABC 2 03-09-2011 Apple 2 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111113 ABC 3 03-09-2011 Apple 3 Unhappy 03-09-2011 Yes 03-09-2011 115
111114 ABC 4 03-09-2011 Apple 4 Unhappy 03-09-2011 Yes 03-09-2011 119
111115 ABC 5 03-09-2011 Apple 5 Unhappy 03-09-2011 Yes 03-09-2011 120
111116 ABC 6 03-09-2011 Apple 6 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111117 ABC 7 03-09-2011 Apple 7 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111118 ABC 8 03-09-2011 Apple 8 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111119 ABC 9 03-09-2011 Apple 9 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111120 ABC 10 03-09-2011 Apple 10 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111121 ABC 11 03-09-2011 Apple 11 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111122 ABC 12 03-09-2011 Apple 12 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111123 ABC 13 03-09-2011 Apple 13 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111124 ABC 14 03-09-2011 Apple 14 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111125 ABC 15 03-09-2011 Apple 15 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111126 ABC 16 03-09-2011 Apple 16 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111127 ABC 17 03-09-2011 Apple 17 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111128 ABC 18 03-09-2011 Apple 18 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111129 ABC 19 03-09-2011 Apple 19 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111130 ABC 20 04-09-2011 Apple 20 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111131 ABC 21 05-09-2011 Apple 21 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111132 ABC 22 06-09-2011 Apple 22 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111133 ABC 23 07-09-2011 Apple 23 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111134 ABC 24 08-09-2011 Apple 24 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111135 ABC 25 09-09-2011 Apple 25 Happy Not Applicable Not Applicable Not Applicable Not Applicable
111136 ABC 26 10-09-2011 Apple 26 Happy Not Applicable Not Applicable Not Applicable Not Applicable
222237 ABC 27 11-09-2011 Apple 27 Happy Not Applicable Not Applicable Not Applicable Not Applicable Attached
**************** above info on sheet1 *************


Number Customer Name Date sold Product sold Product Feedback Date returned Defective Product Date Repaired Product Error Number Comments
111111 ABC 1 03-09-2011 Apple 1 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111112 ABC 2 03-09-2011 Apple 2 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111113 ABC 3 03-09-2011 Apple 3 Unhappy 03-09-2011 Yes 03-09-2011 115 Existing
111113 ABC 3 03-09-2011 Apple 3 Unhappy 03-09-2011 Yes 03-09-2011 115 duplicate
111114 ABC 4 03-09-2011 Apple 4 Unhappy 03-09-2011 Yes 03-09-2011 119 Existing
111114 ABC 4 03-09-2011 Apple 4 Unhappy 03-09-2011 Yes 03-09-2011 119 duplicate
111115 ABC 5 03-09-2011 Apple 5 Unhappy 03-09-2011 Yes 03-09-2011 120 Existing
111116 ABC 6 03-09-2011 Apple 6 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111116 ABC 6 03-09-2011 Apple 6 Happy Not Applicable Not Applicable Not Applicable Not Applicable duplicate
111117 ABC 7 03-09-2011 Apple 7 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111118 ABC 8 03-09-2011 Apple 8 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111119 ABC 9 03-09-2011 Apple 9 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111120 ABC 10 03-09-2011 Apple 10 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111121 ABC 11 03-09-2011 Apple 11 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111122 ABC 12 03-09-2011 Apple 12 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111123 ABC 13 03-09-2011 Apple 13 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111124 ABC 14 03-09-2011 Apple 14 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111125 ABC 15 03-09-2011 Apple 15 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111126 ABC 16 03-09-2011 Apple 16 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111127 ABC 17 03-09-2011 Apple 17 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111128 ABC 18 03-09-2011 Apple 18 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111129 ABC 19 03-09-2011 Apple 19 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111130 ABC 20 04-09-2011 Apple 20 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111131 ABC 21 05-09-2011 Apple 21 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111133 ABC 23 07-09-2011 Apple 23 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111134 ABC 24 08-09-2011 Apple 24 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111135 ABC 25 09-09-2011 Apple 25 Happy Not Applicable Not Applicable Not Applicable Not Applicable Existing
111137 ABC 27 11-09-2011 Apple 27 Happy Not Applicable Not Applicable Not Applicable Not Applicable New
111137 ABC 27 11-09-2011 Apple 27 Happy Not Applicable Not Applicable Not Applicable Not Applicable duplicate
111138 ABC 28 12-09-2011 Apple 28 Happy Not Applicable Not Applicable Not Applicable Not Applicable New
111139 ABC 29 13-09-2011 Apple 29 Happy Not Applicable Not Applicable Not Applicable Not Applicable New
**************** above info on sheet2 **********

 

[SkipVought]

Please explain how duplicate is resolved in columns A B & D
[tt]
A B D J
Number Customer_Name Product_sold Comments
111113 ABC_3 Apple_3 duplicate
111114 ABC_4 Apple_4 duplicate
111116 ABC_6 Apple_6 duplicate
111137 ABC_27 Apple_27 duplicate
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

If

AND {

Sheet1 (Col A) = Sheet2(Col A),
Sheet1 (Col B) = Sheet2(Col B),
Sheet1 (Col D) = Sheet2(Col D)
}

Then

Sheet2 (Col J) = "Duplicate"

 

[visible2you]

I am sorry ... above is applicable for "OLD".

 

[visible2you]

***** CORRECTION *****

If

AND {

Sheet1 (Col A) = Sheet2(Col A),
Sheet1 (Col B) = Sheet2(Col B),
Sheet1 (Col D) = Sheet2(Col D)
}

Then

Sheet2 (Col J) = "OLD"


***** CORRECTED *****

 

[visible2you]

Let me explain "Duplicate"

Let 'a' increments a row number by 1

If

AND {

Sheet2 (An) = Sheet2(An+a),
Sheet2 (Bn) = Sheet2(Bn+a),
Sheet2 (Dn) = Sheet2(Dn+a)
}

Then

Sheet2 (Jn+a) = "Duplicate"

a = a+1

Loop again "IF" aforesaid

End

Here, n refers to n rows

 

[SkipVought]

THAT is the kind of SIGNIFICANT DETAIL that you failed to provide, so we do not have to guess at what you really mean!

Now provide the same detail for each of the other stated criteria!
[tt]
Old: Those values which are same in Col A, B and D of Sheets(1) and Sheets(2).
New: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1)
Updated: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1) and were updated in Sheets(1) using data from Sheets(2).
Attached: Those values in Col A, B and D of Sheets(1) which does not exist in Sheets(2) and were attached in Sheets(2) using data from Sheets(1).
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Your cooperation is sad indeed. I can only assume that you are not really interested in finding a solution.

Given that you have provided examples of duplicate and new only (while existing, has not defined requirement, you have failed to provide examples of old, updated and attached.

This, for instance, is a spreadsheet solution for Sheet2, of the TWO values for which you provided examples...
[tt]
=IF(AND(A2=A1,B2=B1,D2=D1),"duplicate",IF(AND(COUNTIF(Sheet1!A:A,A2)=0,COUNTIF(Sheet1!B:B,B2)=0,COUNTIF(Sheet1!D,D2)=0),"New",""))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

So have you tried to generated ANY code? Please post what you have so far.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

Let me explain "OLD"

Let a=2
Do
If

AND {

Sheet2 (An) = Sheet1(An+a),
Sheet2 (Bn) = Sheet1(Bn+a),
Sheet2 (Dn) = Sheet1(Dn+a)
}

Then

Sheet2 (Jn+a) = "Old"

a = a+1

Loop until each cell in a row = "".

End

Here, n refers to n rows

I will provide detail to other definitions shortly. I really regret that you are so helpful and I am not able to express clearly. I am trying and improving. I need some more time to express other definitions well.

 

[visible2you]

This can be good expression: -
=IF(AND(A2=$A$1,B2=$B$1,D2=$D$1),"duplicate","")

And this code can be extended to multiple rows.

But how to represent this in VBA notation so that Formula does not appear on Sheet?

 

[SkipVought]

This
[tt]
=IF(AND(A2=$A$1,B2=$B$1,D2=$D$1),"duplicate","")
[/tt]
is NOT a good expression as every row value compares to the SAME VALUE which is a HEADING and furthermore, it bears no resemblance to your requirement! Did you see and try my formula???

Before you start coding, you need to have ALL your requirements specified. Are we there yet? What about existing, Updated and Attached?

Please provide examples of DATA for each of these, as you did previously for New and Duplicate.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]