• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stock Scanning

S

srirajsa

Member
sir


Im working for a store, while stock taking we scan all the barcodes but some times we type barcode manually which is like this

Example A

CELLA CELLB (QTY)


0009pa004735201

0009PA004735201

0009pa004735201

0009pa004735204


Example B


CELL A CELL B(QTY)

0009PA004735201 3

0009pa004735204 1


for verifying i need to manually split the barcodes who typed mannualy from Example CELL B


as Example CELL A,


There will be lot of files where I have to split like Example CELL A

Is there any formula to do this


thanks
 
Hi ,


There are two parts to your problem :


1. To retrieve a list of unique items from your manually entered list , which may contain duplicates


2. To count the number of each item in the original list


To solve 1 , refer to the link given below :


http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/


I'll summarise that link's contents here :


a) Assume your data is from A7:A10.


b) Assume your unique list will start from B7 downwards


In B7 , enter the following formula :


=INDEX($A$7:$A$10,MATCH(0,COUNTIF($B$6:B6,$A$7:$A$10),0))


Enter this formula as an array formula , with CTRL SHIFT ENTER.


Copy this formula to the remaining relevant cells in column B ; where the unique entries are made , their codes will appear ; once all the unique codes have appeared , the remaining entries will be #N/A. If you don't want these to be displayed , you can include an =IFERROR function outside the above formula as follows :


=IFERROR(INDEX($A$7:$A$10,MATCH(0,COUNTIF($B$6:B6,$A$7:$A$10),0)),"")


The second requirement is met by using the =COUNTIF function as follows :


=COUNTIF($A$7:$A$10,B7)


Enter this formula in cell C7 , and copy to the remaining relevant cells in column C.


Narayan
 
sir,


may be i asked wrong now i write very simple


for example


fruit qty


apple 2

orange 3

mango 5


what i need if a data is like above formula if possible to generate like this


apple

apple

orange

orange

orange (3 times since qty is 3)

mango

mango

mango

mango

mango (5 time since qty is 5)
 
Hi Srirajsa,


check this thread..


http://chandoo.org/forums/topic/getting-items-with-required-raw-in-next-sheet


Regards,

Prasad
 
Hi NARAYANK991,


I tried the link mentioned in your post for the subject problem and found it working correctly. But just for the sake of enhancing my excel-understanding, i tried to solve the problem (Extracting the unique values to a separate column)by an alternate means.


When i tried the solution with helper columns, it was giving correct result, but when i combined the formulas, it was giving Num Error. Kindly point out what is wrong in combined formula!!!


Combined Formula:

=INDEX($A$3:A11,SMALL(IF(COUNTIF($A$4:A4,A4)=1,ROW(),"0"),ROW(D1))-2)


Here is the sample file:

http://www.2shared.com/file/gG8n2sBT/Book198.html


This pic might help you to understand problem:

http://www.2shared.com/photo/_Co4v3f1/New_Bitmap_Image.html


Thanks

FASEEH
 
Hi Faseeh ,


I'll go through your formulae in detail , but on first sight , the formulae you have used seem to be different from what was posted.


Narayan
 
Hi NARAYANK991,


Yes your are right, It is different from what has been mentioned in the cited link. It is working fine with helper columns, only problem that occurs is when i try to merge formula into one.


Regards,

FASEEH
 
Hi Faseeh ,


I think the basic problem is that the final formula has to look at the full array in column C , since you are taking the smallest of the array of values in column C , one after the other ( the incrementing is done by using ROW() , which increments as you copy it downwards in the column.


The formula which has been used :


INDEX($A$4:$A$12,SMALL($C$4:$C$12,ROW(M1))-3)


works only because within the IF statement , you have used "0" instead of 0 ; the correct value should be a very high number , such as 99.


ROW(M1) returns 1 ; the smallest value within column C is 4 , and 4-3 = 1 , which is why the first value in the list is the smallest value ; the next value will be the next smallest and so on.


When the IF statement , which returns only one value at a time , is combined within the INDEX function , the SMALL function does not get the whole array to work with , consequently , it cannot work correctly.


Another way of looking at it is that the method of using helper columns relies on two techniques ; the formula :


=COUNTIF($A$4:A4,A4)


where the first A4 is used absolutely , and the second A4 is used relatively , ensures that the count for the first occurrence of every value ( whether unique or not ) will be 1 , and greater than 1 for subsequent occurrences. Hence the COUNTIF will not work if you use it to return the count for the whole array at a time.


The SMALL formula , on the other hand , has to work with the whole array !


Combining these two techniques in one formula may not be possible ; if you really want to implement this algorithm using one formula , you might have to use a different approach.


Narayan
 
Hi NARAYANK991,


Thanks for yours pains taking effort of explaining this formula. While I had been trying hard to reach what is going wrong in it, i found several times the small() function feed with a single value, since no second option was available to give it second largest no. hence it was giving num error. Your explanation has just made it even more clearer!!! Thanks..


Faseeh
 
You must log in or register to reply here.
Back
Top