Comparing and extracting

[tycanis]

This is my first post on here so bear with me.

I have an excel spreadsheet that is auto generated whenever I want. What is produced is a list of item numbers in one column and then either V or } to indicate if the item received an possession scan or a stock scan. If an item received both the same item number shows up in two different rows on with a V and one with a }.

I have a report that determines what received a } without getting a V but I want to do the reverse. I cannot change the report because it is provide for me and I do not have access to its programming so I am trying to do this with the spreadsheet I can generate on my own.

How can I take my data and have returned to me what item numbers have not received a V but did get a }? The generated report is very simple but is sometimes 50000 rows. Thanks.

 

[bryonwoods30]

The easiest way I could think of doing it is as follows.

If your item numbers are in column a, and the V/} are in column b do the following

1. in a new sheet, copy columns a and b to it so it looks like your original
2. highlight column b, replace the V's with 1, } with 2
3. copy your item numbers again in column c
4. in column d, use the formula =SUMIF(A:A,C1,B:B) and drag it down

the items that have a 1 by them have v's only, the items with 2 by them have } only and the items with 3 by them have both.
you can then just filter on column d.

 

[tycanis]

That idea does work but I am trying to make it so that I do not have to manipulate the sheet a great deal everytime I generate it. I might be pulling this 5 or 6 times over the course of 5 hours and would like to be able to get the results in a very short time. I want to be able to share this with others who generate the report for themselves and do not want it to be too involved if that makes sense.

 

[tycanis]

Here is an example of the data if it helps.

A B C D E F
0 12345 545 20110312 412 }
454 23435 545 20110312 412 }
454 56789 545 20110312 413 }
454 23435 545 20110312 418 V
454 56789 545 20110312 423 V


Column A is used to indicate if B is a valid barcode, B is the item number from the barcode, C is a location number, D is the date, E is the time, and F is the scan type.

The data is generated in ascending order according to time and date. I will use Column A to determine if I need to look at B and then F but for 50000 rows it is extremely time consuming to use. A formula that would tell me if something was scanned with both a } and a V would be wonderful.

 

[bryonwoods30]

in column c1 use formula =COUNTIF(A:A,A1) then just double click on the black box in the bottom right of the cell so it copies the formula all the way down to your last cell.
then just do filter auto filter. filter column c for the things that only show up 1 time and then apply the filter on column b for the values that only have }