Hello!

Can someone provide a suggestion for the following scenario? I have a list
like the following (or I will, once I apply a macro solution I found in
another post). I'd like to scan through the list, insert a row at each
change in value in Column1 (column headers don't actually exist in my
worksheet), and insert a lookup formula in Column 2 of the newly inserted row
which uses the value in Column 1 to lookup a corresponding value on another
worksheet.

Column1 Column2 Column3
1000 Requirements 40
1000 Design 5
1008 Requirements 10
1008 Design 8

Run a macro that produces:

Column1 Column2 Column3
1000 <Vlookup formula
1000 Requirements 40
1000 Design 5
1008 <Vlookup formula
1008 Requirements 10
1008 Design 8

I hope this makes sense. I know all you smart people out there have
solutions if I can make this clear. Thanks in advance!

How about this:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
.Rows(1).Insert
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'do nothing
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = .Cells(iRow + 2, "A").Value
.Cells(iRow + 1, "B").FormulaR1C1 _
= "=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"
End If
Next iRow
.Rows(1).Delete
End With

End Sub

Be careful. This line is actually in R1C1 reference style:
"=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"

It's looking for sheet2 columns 1 and 2 (A:B).


DWeb wrote:

Hello!

Can someone provide a suggestion for the following scenario? I have a list
like the following (or I will, once I apply a macro solution I found in
another post). I'd like to scan through the list, insert a row at each
change in value in Column1 (column headers don't actually exist in my
worksheet), and insert a lookup formula in Column 2 of the newly inserted row
which uses the value in Column 1 to lookup a corresponding value on another
worksheet.

Column1 Column2 Column3
1000 Requirements 40
1000 Design 5
1008 Requirements 10
1008 Design 8

Run a macro that produces:

Column1 Column2 Column3
1000 <Vlookup formula
1000 Requirements 40
1000 Design 5
1008 <Vlookup formula
1008 Requirements 10
1008 Design 8

I hope this makes sense. I know all you smart people out there have
solutions if I can make this clear. Thanks in advance!

--

Dave Peterson

Thank you! This worked like a charm and saved me a ton of time and effort.
Big help.

Now I have another question. I am building this data to load into MS
Project and have populated a number of other columns for the additional
summary row you helped me to programatically insert to my list. One of those
columns is "Outline Level" which is now being populated with "2" for the
original tasks and "1" for the new summary tasks being inserted by the macro
you provided.

I would like to sort all of the summary rows based on 3 of the columns,
while maintaining the position of the "sub-tasks" (outline level 2) under
each of the summary tasks, effectively sorting about 100 7 row "blocks" by
the data in row 1 of each block.

Do you have any ideas on how to do this within a macro?

Thanks again for all your help!

"Dave Peterson" wrote:

How about this:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
.Rows(1).Insert
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'do nothing
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = .Cells(iRow + 2, "A").Value
.Cells(iRow + 1, "B").FormulaR1C1 _
= "=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"
End If
Next iRow
.Rows(1).Delete
End With

End Sub

Be careful. This line is actually in R1C1 reference style:
"=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"

It's looking for sheet2 columns 1 and 2 (A:B).


DWeb wrote:

Hello!

Can someone provide a suggestion for the following scenario? I have a list
like the following (or I will, once I apply a macro solution I found in
another post). I'd like to scan through the list, insert a row at each
change in value in Column1 (column headers don't actually exist in my
worksheet), and insert a lookup formula in Column 2 of the newly inserted row
which uses the value in Column 1 to lookup a corresponding value on another
worksheet.

Column1 Column2 Column3
1000 Requirements 40
1000 Design 5
1008 Requirements 10
1008 Design 8

Run a macro that produces:

Column1 Column2 Column3
1000 <Vlookup formula
1000 Requirements 40
1000 Design 5
1008 <Vlookup formula
1008 Requirements 10
1008 Design 8

I hope this makes sense. I know all you smart people out there have
solutions if I can make this clear. Thanks in advance!

--

Dave Peterson

Try this against a copy of your data.

Show level 2 (not the details, right?). Then select your range and do
data|sort.

Did that work ok for you?

Excel is pretty smart when it works with hidden rows from both data|group and
Data|sort.

DWeb wrote:

Thank you! This worked like a charm and saved me a ton of time and effort.
Big help.

Now I have another question. I am building this data to load into MS
Project and have populated a number of other columns for the additional
summary row you helped me to programatically insert to my list. One of those
columns is "Outline Level" which is now being populated with "2" for the
original tasks and "1" for the new summary tasks being inserted by the macro
you provided.

I would like to sort all of the summary rows based on 3 of the columns,
while maintaining the position of the "sub-tasks" (outline level 2) under
each of the summary tasks, effectively sorting about 100 7 row "blocks" by
the data in row 1 of each block.

Do you have any ideas on how to do this within a macro?

Thanks again for all your help!

"Dave Peterson" wrote:

How about this:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
.Rows(1).Insert
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'do nothing
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = .Cells(iRow + 2, "A").Value
.Cells(iRow + 1, "B").FormulaR1C1 _
= "=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"
End If
Next iRow
.Rows(1).Delete
End With

End Sub

Be careful. This line is actually in R1C1 reference style:
"=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"

It's looking for sheet2 columns 1 and 2 (A:B).


DWeb wrote:

Hello!

Can someone provide a suggestion for the following scenario? I have a list
like the following (or I will, once I apply a macro solution I found in
another post). I'd like to scan through the list, insert a row at each
change in value in Column1 (column headers don't actually exist in my
worksheet), and insert a lookup formula in Column 2 of the newly inserted row
which uses the value in Column 1 to lookup a corresponding value on another
worksheet.

Column1 Column2 Column3
1000 Requirements 40
1000 Design 5
1008 Requirements 10
1008 Design 8

Run a macro that produces:

Column1 Column2 Column3
1000 <Vlookup formula
1000 Requirements 40
1000 Design 5
1008 <Vlookup formula
1008 Requirements 10
1008 Design 8

I hope this makes sense. I know all you smart people out there have
solutions if I can make this clear. Thanks in advance!

--

Dave Peterson


--

Dave Peterson

Dave,

What do you mean by "show level 2"? Did you mean apply an autofilter and
filter "Outline Level" to only show the rows with the value of "2" in that
column?. What did you mean by "select your range".

If I understood your meaning, I filtered to only show rows with "2" in the
Outline Level column, then selected all and sorted by my 3 fields. That did
not work, as the Level 2 rows no longer match up with the proper level 1 row.

FYI - the first column holds "Request Number" which is the same value for
each "block" of seven rows.

Sorry if I misunderstood your suggestion - please let me know if I didn't
follow instructions correctly. Thanks!

"Dave Peterson" wrote:

Try this against a copy of your data.

Show level 2 (not the details, right?). Then select your range and do
data|sort.

Did that work ok for you?

Excel is pretty smart when it works with hidden rows from both data|group and
Data|sort.

DWeb wrote:

Thank you! This worked like a charm and saved me a ton of time and effort.
Big help.

Now I have another question. I am building this data to load into MS
Project and have populated a number of other columns for the additional
summary row you helped me to programatically insert to my list. One of those
columns is "Outline Level" which is now being populated with "2" for the
original tasks and "1" for the new summary tasks being inserted by the macro
you provided.

I would like to sort all of the summary rows based on 3 of the columns,
while maintaining the position of the "sub-tasks" (outline level 2) under
each of the summary tasks, effectively sorting about 100 7 row "blocks" by
the data in row 1 of each block.

Do you have any ideas on how to do this within a macro?

Thanks again for all your help!

"Dave Peterson" wrote:

How about this:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
.Rows(1).Insert
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'do nothing
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = .Cells(iRow + 2, "A").Value
.Cells(iRow + 1, "B").FormulaR1C1 _
= "=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"
End If
Next iRow
.Rows(1).Delete
End With

End Sub

Be careful. This line is actually in R1C1 reference style:
"=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"

It's looking for sheet2 columns 1 and 2 (A:B).


DWeb wrote:

Hello!

Can someone provide a suggestion for the following scenario? I have a list
like the following (or I will, once I apply a macro solution I found in
another post). I'd like to scan through the list, insert a row at each
change in value in Column1 (column headers don't actually exist in my
worksheet), and insert a lookup formula in Column 2 of the newly inserted row
which uses the value in Column 1 to lookup a corresponding value on another
worksheet.

Column1 Column2 Column3
1000 Requirements 40
1000 Design 5
1008 Requirements 10
1008 Design 8

Run a macro that produces:

Column1 Column2 Column3
1000 <Vlookup formula
1000 Requirements 40
1000 Design 5
1008 <Vlookup formula
1008 Requirements 10
1008 Design 8

I hope this makes sense. I know all you smart people out there have
solutions if I can make this clear. Thanks in advance!

--

Dave Peterson


--

Dave Peterson

I thought you were using Data|Group a couple of times to show an outline on that
left hand side. So data|autofilter wouldn't be necessary.

And select your range means to select the range you want sorted. Top left cell
(of the last headerrow) to the bottom right cell of the real data.



DWeb wrote:

Dave,

What do you mean by "show level 2"? Did you mean apply an autofilter and
filter "Outline Level" to only show the rows with the value of "2" in that
column?. What did you mean by "select your range".

If I understood your meaning, I filtered to only show rows with "2" in the
Outline Level column, then selected all and sorted by my 3 fields. That did
not work, as the Level 2 rows no longer match up with the proper level 1 row.

FYI - the first column holds "Request Number" which is the same value for
each "block" of seven rows.

Sorry if I misunderstood your suggestion - please let me know if I didn't
follow instructions correctly. Thanks!

"Dave Peterson" wrote:

Try this against a copy of your data.

Show level 2 (not the details, right?). Then select your range and do
data|sort.

Did that work ok for you?

Excel is pretty smart when it works with hidden rows from both data|group and
Data|sort.

DWeb wrote:

Thank you! This worked like a charm and saved me a ton of time and effort.
Big help.

Now I have another question. I am building this data to load into MS
Project and have populated a number of other columns for the additional
summary row you helped me to programatically insert to my list. One of those
columns is "Outline Level" which is now being populated with "2" for the
original tasks and "1" for the new summary tasks being inserted by the macro
you provided.

I would like to sort all of the summary rows based on 3 of the columns,
while maintaining the position of the "sub-tasks" (outline level 2) under
each of the summary tasks, effectively sorting about 100 7 row "blocks" by
the data in row 1 of each block.

Do you have any ideas on how to do this within a macro?

Thanks again for all your help!

"Dave Peterson" wrote:

How about this:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
.Rows(1).Insert
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = .Cells(iRow + 1, "A").Value Then
'do nothing
Else
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = .Cells(iRow + 2, "A").Value
.Cells(iRow + 1, "B").FormulaR1C1 _
= "=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"
End If
Next iRow
.Rows(1).Delete
End With

End Sub

Be careful. This line is actually in R1C1 reference style:
"=vlookup(rc[-1],'sheet2'!c1:c2,2,false)"

It's looking for sheet2 columns 1 and 2 (A:B).


DWeb wrote:

Hello!

Can someone provide a suggestion for the following scenario? I have a list
like the following (or I will, once I apply a macro solution I found in
another post). I'd like to scan through the list, insert a row at each
change in value in Column1 (column headers don't actually exist in my
worksheet), and insert a lookup formula in Column 2 of the newly inserted row
which uses the value in Column 1 to lookup a corresponding value on another
worksheet.

Column1 Column2 Column3
1000 Requirements 40
1000 Design 5
1008 Requirements 10
1008 Design 8

Run a macro that produces:

Column1 Column2 Column3
1000 <Vlookup formula
1000 Requirements 40
1000 Design 5
1008 <Vlookup formula
1008 Requirements 10
1008 Design 8

I hope this makes sense. I know all you smart people out there have
solutions if I can make this clear. Thanks in advance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson