Compare data from sheets and input text [SOLVED]

[IKHAN]

Hi, Hope i can explain my issue below, Any help would be really appreciated.


when i copy data or enter data into sheet 1 column A5 ,A6,A7...upto A1000 as REQD., It should check with pre inputted data in sheet 4 column A and if there is a match of 2 exact same item ,It should fill "TEXT" in sheet 1 column G accordingly...see example below


EXAMPLE


note : SHEET1 HAS HEADER ROW AND DATA STARTS FROM A5


SHEET 1(COL A5 )-- SHEET 4(COL A) PRE INPUTTED DATA


APPLE --- APPLE

APPLEB ---- blank

LEMON --- Appleb


example : RESULT REQD IN SHEET 1 column G


COLUMN A ...... COLUMN G


If APPLE entererd in column A ....... DISPLAY IN COLUMN G "PRIME"

If APPLEB entererd in column A ....... DISPLAY IN COLUMN G "SECOND"

If lemon entererd in column A ....... since data not available in sheet 4 COL A - DISPLAY IN COLUMN G "NONE"

 

[IKHAN]

sample file attached


https://hotfile.com/dl/224121646/9c835e8/sheet_compare.xlsx.html

 

[SirJB7]

Hi, IKHAN!


The method is very different if you input data or you copy and paste data. If you enter data on individual cells you can trap it with the change event (macro 1) and you'd be seeing the result after each input. If you paste data you'll have to manually run a procedure that does the job (macro 2).


An alternative is to place a formula in column G like:

=SI(ESNOD(BUSCARV(A5;'pre input'!A:A;1;FALSO));"None";SI(DERECHA(A5;1)="B";"Second";"Prime")) -----> in english: =IF(ISNA(VLOOKUP(A5,'pre input'!A:A,1,FALSE)),"None",IF(RIGHT(A5,1)="B","Second","Prime"))

and copy down as needed.


Regards!

 

[IKHAN]

hi SirJB7,


i used the formula provided and copied down, Formula works for prime and second except that cell shows as "None" even if no data is entered in column A (sheet 1), Cell(column G) should show as "NONE" ONLY if no match data found in Pre input sheet.


And yes i have to paste data(in thousands) and sometimes enter manually...

 

[SirJB7]

Hi, IKHAN!


I should download again the file and as it's uploaded at hoftile and I have to wait half an hour (after finishing) as I'm downloading another file. It's comfortless to handle with sites with this kind of policies for free users while DropBox or other ones make things easier.


But if I didn't understand wrongly you may try changing the formula to this:

=SI(A5="";"";SI(ESNOD(BUSCARV(A5;'pre input'!A:A;1;FALSO));"None";SI(DERECHA(A5;1)="B";"Second";"Prime"))) -----> in english: =IF(A5="","",IF(ISNA(VLOOKUP(A5,'pre input'!A:A,1,FALSE)),"None",IF(RIGHT(A5,1)="B","Second","Prime")))

... or copy only to the related rows of column G accordingly to column A.


Regards!

PS: Yeap, I emptied my recycle bin yet.

 

[IKHAN]

Hi SirJB7,


It worked....Thanks so much

 

[SirJB7]

Hi, IKHAN!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!