data sorting question

[albertpinto]

I've tried to achieve this problem with formulas and lookups and if
statements and conditinal formatting, if statements however was no
able to achieve
desired results, so asking for help since I am not good with XL
marcros..if someone can start me up I can navigate myself..

There is a list of numbers and there is a possiblity that
numbers will be repeats like:
12345 fail
12345 fail
12345 pass
11111 fail
11111 pass
22222 pass
33333 fail
33333 fail
44444 pass

I want to read say A1 (numbers) disregard the repeats and put info A2
A3, A4 etc etc

12345 fail fail pass
11111 fail pass
22222 pass
33333 fail pass
44444 pass

if anyone can help out it will be gr8ly appreciated.

 

[Ken Wright]

OK, silly question, but are you really sure you want to do this. Why would you
want to repeat a word such as fail or pass numerous times, instead of perhaps
going for something like the following:-

Fail Pass
12345 2 1
11111 1 1
22222 0 1
33333 1 1
44444 0 1

Assuming this would be viable, then give your data two headings, ie ID and
Pass/Fail, then simply select all your data, do Data / Pivot table and
pivotChart report, hit Next / Next / Finish. Drag the ID field to the left
where the row fields go, drag the Pass/Fail field to where the Column fields go,
and then drag the pass/fail field once again, but this time into the Data area.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[albertpinto]

Hi Ken..

The reason I want to repeat P/F is because I want to track exactly ho
many times the particular SN fails before it passes, sort of like
history also extract 1st 2nd and 3rd time passes, I did your approac
already and yes it will give me overall data what I want however wil
not give me 1st 2nd p/f statistics.

Thanks for your respons

 

[Ken Wright]

OK then, see if this is of any use:-

With your data in Col A:B, headers in row 1

in C1 put a header of 'Order'
in D1 put a header of PF

In C2 put =COUNTIF($A$2:A2,A2) and copy down
In D2 put =IF(B2="pass",1,-1) and copy down

Pivot table and then drag S/N to the row field, Order to the column field, and
PF to the Data field

Now select all the data in the data field (Not in the row or column fields) and
format the cells as custom "Pass";"Fail"

 

[albertpinto]

Yes Ken this is what I wanted to see..You are the best..Thanks a Lot..

Thanks agai

 

[Ken Wright]

LOL - you're welcome