Occurrence to equal 1

[Busymanjohn]

Hi all, I have a file that shows over 13,000 lines of data, the data is to be used to work out storage space, but that is irrelevant at this point. Some of the data shows that some part numbers have an occurrence of 4, 7, 10 etc etc .... what I want to do is split these rows out ,,,, so if a part number has an occurrence of 7 I want to split that out to 7 rows and not just one row ( part number has a qty of 7 and appears as one line in the data, I want to show that as seven separate rows with a qty of 1 each ) .... make sense?

 

[AVK]

Dear Busymanjohn, for more clarity plz upload your sample file with in sheet1 before data & required result / output mentioned manally in sheet2 .

 

[Busymanjohn]

File attached

 

[Hui]

Strangely, This was asked by someone else several days ago
Please refer to the solutions presented here: http://forum.chandoo.org/threads/itemized-output-table-from-batch-input-table.32085/

 

[Busymanjohn]

Hi Hui, I don't see how the formula(s) ... I don't have Power Pivot .... can be used in my data, or I am missing something.

 

[Hui]

There are a number of solutions there not just mine

 

[bosco_yip]

Try,

In Cell I4, formula copy down :

=LOOKUP(ROWS($1:1),SUMIF(OFFSET(C$3,,,ROW($1:$6),),"<>")+1,B$4:B$8)&""

In Cell J4, formula copy down :

=IF(I4<>"",1,"")

Regards