How to apply a blank row after every contact name

[chandannasta]

How to apply a blank row as shown below after every contact name in an excel spreadsheet if you have data of more then 10000 ppl. I have attached the sample spreadsheet for the same.Is there any formula that we can apply or something like that please let me know in order to save time

[Blake 7]

Oh - sorry, this is to insert a blank row every other row.. and not when the names changes - sorry I misread

Please Login or Register  to view this content.

[estige]

Hi, one way is to use helper columns:

D-column counts number of names
D2: =IF(ROW()=2,1,IF(A2=A1,E1,E1+1))
Copy down

E-column counts number of rows for each name (first row is value 2)
E2:=IF(A2=A1,D1+1,2)
Copy down

Now add the necessary nr of rows at the bottom of your list with all unique values from SORT1 except "1" in D-column and the value 1 in E-column, (unique values from SORT1 is from 2 to number of unique names, in your example 3.). You can now sort the list by SORT1 and SORT2. The rows you entered at the bottom will now end up between rows with different names.

You can make a list with values for SORT1 for the new rows manually (fast and easy) or this way:
I2:=IF(MIN(D:D+1)+ROW()-2>MAX(D:D),"",MIN(D:D+1)+ROW()-2)
copy down as far as necessary (number of rows = number of unique names)

[Blake 7]

This should work..

Please Login or Register  to view this content.

[xlrocks]

1) in a empty column - mark row where the name is changing (using if function)
2) Filter that column for 1, select the data
3) Select visible cells.
4) Right click and "insert Row"
5) you will be happy to see the visuals when Excel inserts rows. and its Done

Hope you will be able to do this. If u require more information pls let me know, I will give more detailed explanation.

[satputenandkumar0]

or try,

Please Login or Register  to view this content.

[chandannasta]

1) in a empty column - mark row where the name is changing (using if function)
2) Filter that column for 1, select the data
3) Select visible cells.
4) Right click and "insert Row"
5) you will be happy to see the visuals when Excel inserts rows. and its Done

Hope you will be able to do this. If u require more information pls let me know, I will give more detailed explanation.

I couldn't understand the If function thing. Please if you can clarify this please. If you see my thread I have also attached a spreadsheet. If you can do in that and show it to me I would be grateful to you with the of formula that you are saying.

[chandannasta]

or try,

Please Login or Register  to view this content.

Yr Macro was really good, but I dont know Y it stopped at cell #20852 and after that it didnt do anything. So i tried to copy from that row onwards till the last row of that particular tab in a different tab on the same spreadsheet but it didn't work at all. It used to give me error 400 all the time. I even opened a new excel sheet on a different screen and tried in that but always got error 400 when doing the 2nd time. The 1st time it works good 2nd time it doesnt work at all.

[chandannasta]

Oh - sorry, this is to insert a blank row every other row.. and not when the names changes - sorry I misread

Please Login or Register  to view this content.

@Blake, today i was doing another project in which I had to enter blank row manually after every row after all the details were entered correctly. Suddenly I remembered about your Macro which you had given above and this helped me saved a lot of time on my today's project. Only problem it stopped at cell #40000. It doesn't go beyond that. Tried on a different spreadsheet, also on the same spreadsheet different tab but hard luck got error 400 all the time. But thanks for this Macro also. Its really valuable for me But if you can do something about rest of the rows after #40000 then it would be really useful