Using a macro:
In a worksheet I would like for a row to be inserted if in a column the next
number is different.
example: in Column E
Before After
03279090 03279090
03279090 03279090
03299880
03299880 03299880
03299880 03299880
03299880
TFTH,
Tom
Using a macro:
In a worksheet I would like for a row to be inserted if in a column the next
number is different.
example: in Column E
Before After
03279090 03279090
03279090 03279090
03299880
03299880 03299880
03299880 03299880
03299880
TFTH,
Tom
To, the following macro should do your job:
Sub InsertRows()
StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 5 'Change the 1 to the column where your data is
i = StartRow + 1
While Cells(i, DataColumn) <> ""
If Cells(i, DataColumn) <> Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
i = i + 1
End If
i = i + 1
Wend
End Sub
-------------------------------------
To run it:
Alt+F11 for the VB editor
menu command Insert | Module
Paste the code above
>From Excel: Alt+F8
Or without VBA...
Enter this formula at B2 and copy down:
=--(A1=A2)
Copy > Paste Special > Value
Go To > Special > Column Differences
Insert > Entire Row
This seems to work except when there is a single instance interspersed in the
data.
If there is a single instance, it appears to insert 2 rows above the 1st
difference and none above the 2nd.
3279090
3279090 TRUE
3280000 FALSE
3299880 FALSE
3299880 TRUE
3299880 TRUE
Otherwise, that was a very interesting trick.
--
Kevin Vaughn
"Herbert Seidenberg" wrote:
> Or without VBA...
> Enter this formula at B2 and copy down:
> =--(A1=A2)
> Copy > Paste Special > Value
> Go To > Special > Column Differences
> Insert > Entire Row
>
>
I noticed I hadn't entered my formula exactly like yours, but even after
making the change, result was the same (I hadn't used the --() construct.)
--
Kevin Vaughn
"Kevin Vaughn" wrote:
> This seems to work except when there is a single instance interspersed in the
> data.
> If there is a single instance, it appears to insert 2 rows above the 1st
> difference and none above the 2nd.
>
> 3279090
> 3279090 TRUE
>
>
> 3280000 FALSE
> 3299880 FALSE
> 3299880 TRUE
> 3299880 TRUE
>
> Otherwise, that was a very interesting trick.
> --
> Kevin Vaughn
>
>
> "Herbert Seidenberg" wrote:
>
> > Or without VBA...
> > Enter this formula at B2 and copy down:
> > =--(A1=A2)
> > Copy > Paste Special > Value
> > Go To > Special > Column Differences
> > Insert > Entire Row
> >
> >
Kevin,
Thanks for pointing out the exception.
Here is a procedure that tolerates non-repeating data.
Enter this formula into B2 and copy down:
=--NOT((A1<>A2)*(B1=1))
Enter this formula into C2 and copy down:
=--(--(A1=A2)=B2)
Select the data in B and C and
Copy > Paste Special > Value
Go To > Special > Column Differences
Insert > Entire Row
This now opens up a whole lot of possibilities, like
inserting blanks at alternate rows.
Herb
That works for me up until the point where I actually insert rows. When I go
to special / column differences, I can tell that it is selecting the rows
that should make it work (ones with differences,) but when I hit insert rows
(or insert entire rows) it does not insert rows where I expect it to. I am
using 2000. Is that the difference?
--
Kevin Vaughn
"Herbert Seidenberg" wrote:
> Kevin,
> Thanks for pointing out the exception.
> Here is a procedure that tolerates non-repeating data.
> Enter this formula into B2 and copy down:
> =--NOT((A1<>A2)*(B1=1))
> Enter this formula into C2 and copy down:
> =--(--(A1=A2)=B2)
> Select the data in B and C and
> Copy > Paste Special > Value
> Go To > Special > Column Differences
> Insert > Entire Row
>
> This now opens up a whole lot of possibilities, like
> inserting blanks at alternate rows.
> Herb
>
>
Try inserting a 1 into B1 and C1.
Otherwise, please post the number sequence you are using
and point to the place where you expect different results.
This did the trick. However, to make sure, I went in and deleted the entries
in B1 and C1, re-entered the formulae and tried the sequence again and it
also worked. So apparently at some point, I took a wrong step that
propogated to future testings. Sorry about that and thanks.
--
Kevin Vaughn
"Herbert Seidenberg" wrote:
> Try inserting a 1 into B1 and C1.
> Otherwise, please post the number sequence you are using
> and point to the place where you expect different results.
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks