Hello all,
I have a group of data in a worksheet. Is there a formula that I can use to
insert a blank row that separate data that are alike?
Here's my sample data:
PO/NO Quantity
1. 6135008885 10500
2. 6135008885 4284
3. 6135008885 3896
4. 6135008886 4320
5. 6135008886 3780
6. 6135008886 10500
7. 6135008887 10500
8. 6135008887 4032
9. 6135008887 3868

How can I insert a blank row between row's 3 and 4 and row's 6 and 7?

Help appreciated.

Cheers,

Wendy

assuming you are not looking for something quite as simple as
higlighting the row below where you want the space and clicking on
Insert Row, you could maybe write an if statement that would compare
above and below and take the next row if different but what I would do,
for a quick result, is one of the following -

Either
use the data subtotals menu options which would not only break up your
data but also subtotal it (or average, or count etc)

Or
use the Data, Filter, Advanced filter options to do a unique extract of
the part numbers - apend this to the bottom of the list and then sort by
the whole part number column - the appended unique records create a
blank line for each part number - to make sure they always come at the
end put another helper column in (before you sort) for these appended
records and use that as the secondary sort.. If you want the line to
be completely blank then copy the part number into a helper column,
apend the unique records to the bottom, sort the column (remembering to
include all the other columns) and then delete the helper column.

R


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=394215

Hello,

Data subtotal doesn't work for me as I have to save the file as txt file and
then import the data to an accounting system. That is why I need a blank row
between different PO no. So the system picks us the correct info for each PO.


I tried the advanced filter. Nothing happened. Maybe I am missing something
here. Would you mind walk me thru the steps by using the below data?

Thanks for your help.

Wendy

"Ruthki" wrote:


assuming you are not looking for something quite as simple as
higlighting the row below where you want the space and clicking on
Insert Row, you could maybe write an if statement that would compare
above and below and take the next row if different but what I would do,
for a quick result, is one of the following -

Either
use the data subtotals menu options which would not only break up your
data but also subtotal it (or average, or count etc)

Or
use the Data, Filter, Advanced filter options to do a unique extract of
the part numbers - apend this to the bottom of the list and then sort by
the whole part number column - the appended unique records create a
blank line for each part number - to make sure they always come at the
end put another helper column in (before you sort) for these appended
records and use that as the secondary sort.. If you want the line to
be completely blank then copy the part number into a helper column,
apend the unique records to the bottom, sort the column (remembering to
include all the other columns) and then delete the helper column.

R


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=394215