Formula for a continuously changing array

[gururajendrak]

Hi,

To explain in brief, i have a series of barcoded labels say 100 no.s , when ever i scan the barcode the data from it gets into the excel file from Cell A1 to A100... and so on.

Now, say i have scanned only 20.. then 21st barcode data in Cell A21 should be checked with previous 20 cells above.

If you have understood my query , pls help me with a formula or suggestion.

If you are not clear ... pls reply back so that i can rephrase my question.

Its very critical for me, i am clueless and need your atmost support.

Thanks
Guru

[:) Sixthsense :)]

Now, say i have scanned only 20.. then 21st barcode data in Cell A21 should be checked with previous 20 cells above.

Needs clear description on the above quoted lines for better understanding

[gururajendrak]

Pls see the attached excel file for better understanding.. Its just a samples but i am looking at a data that would be about 5000 distinct values or more....

[:) Sixthsense :)]

Please describe about your expected output with some data for better understanding

[SDCh]

Try this:

Please Login or Register  to view this content.

[gururajendrak]

Hi Sixth Sense.. imagine there are 100 components with barcodes.. now i am scanning each barcode and that barcode's info comes in a single column(different rows) as shown in the file.

I start scanning with the 1st component ( barcode) the data gets into the excel file.. now i scan the second one and the formula should check the 1st component barcode and see if its unique or not . And so on for the 100 components..

Here i can use the formula "VLOOKUP" or "Match" to look up, if all the 100 barcodes are available readily in an array. but my problem is it not a fixed array, every time i scan a new barcode.. it adds up another row into the array.

The output needed is " Avery new data in Column A gets added should be checked with all the previous rows and say whether its unique or repeated. Keeping in mind that its a dynamic array which keeps on changing as i scan

Hope i am able to explain better... pls dont hesitate if you have not got it.. i will try again..

[:) Sixthsense :)]

Everything is clear except what you want? and how you want?

Can you please describe it?

[gururajendrak]

Great!!!... I will tell you what's the purpose of this exercise...

We make lot of small components, i dont want any component to go with a repeated serial number in barcode to a customer. I have a scanner + want to use excel to verify the uniqueness. Not able to spend money on equipment or software to verify barcodes..

for ex my data from barcode is ABC12345 which is in the cell A15 , so vlookup will look for this number from A1 to A14 and if its repeated i will use a if clause to say repeated or unique.

The next component i scan will go in to cell A16 with the number ABC12346 , In the vlookup array it should increment the number from A1 to A15 instead of looking it in A1 to A14.

The next scan will add in to Cell A17 and this should be looked up in the array A1 to A16 and so on......

Did i answer your Q?????

By the way thanks for your patience....

[:) Sixthsense :)]

Very clear now and thanks for briefing it.

Last question can you please post your vlookup formula for review? please mention in which cell the formula belongs to and upto which? also post the if formula you are using for showing the duplicate identification.

[gururajendrak]

Hi , pls find the format which i am trying to make this.. Pls ignore colum A and B ... i was just trying on my own to make the variable array by linking some things.. it is not working as intended though...

[SDCh]

My answer on post #5 will do the trick, you just need little change for that.

[gururajendrak]

Hi SDCh.... thanks a million.. i never expected such a simple answer... you are awesome... Thanks again. keep rocking..

Hi Sixthsense... I would thank you too for supporting.. i am still open to your suggestions....

Nice interacting with you... Will get back in some time with new challenges..

[:) Sixthsense :)]

I just not replied since SDCh shown the right way to achieve it.

If you would like to know my approach then

In 2nd Row

Please Login or Register  to view this content.

Drag it down...

Thanks for the rep

[gururajendrak]

Hi , I also need another help.. now that you understand what i want completely.. can you help me create a pop up msg on screen when the out put of the formula says "Repeated".

Pls help i am a big "0" in macros...

Thanks in advance

[:) Sixthsense :)]

Sure... just confirm me the exact column reference on which the barcode's are entered......

[gururajendrak]

Barcodes will be populated in column B.

[gururajendrak]

Hi Sixthsense.. I have gone through your logic as well , it also works perfectly.. Thanks i know 2 logics now.... thanks to Excel forum

[:) Sixthsense :)]

Copy the below code and Do right click on sheet tab and select view code and paste it and close the VBA window and return to excel and try inputting Unique and Duplicate Values in Column-B and check.

Please Login or Register  to view this content.

[gururajendrak]

Hi... its perfect.... thanks a TON


off the line:how can i learn macros.. i can record a few small ones with the record macro option but cannot write it.. Pls suggest if there are any books/training material/videos to start off with.

[SDCh]

See this page:
http://www.excelforum.com/excel-prog...materials.html

Regards
SDCh

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

Regarding the material please go through the link provided by SDCH

[gururajendrak]

Thanks to both of you.... I will try going through the material....