Need help with creating a macro that will insert a new column and extract the first value

T

thlee1122

New Member
Joined
Aug 6, 2015
Messages
8
I need help with creating a dynamic macro that will insert a new column on the side and extract the first "expected cost" of each "model" and delete the rest of the rows.


Below is the part of the dataset that I need to use to create this macro.


Here is the example/result that I am looking for.
As you can see on the bottom, there are five different models. Each model has 2 or 3 expected cost.
I want to create a dynamic macro that will extract the first expected cost for each model and put them into separate column.
So, Model: WM3370HWA.ABWEPUS will have expected cost of 4, WM4270HWA.ABWEPUS will have expected cost of 1693, WM8000HWA.ABWEEUS will have expected cost of 1147 and so on.


Your help will be greatly appreciated.


Model Expected Cost(Editable)
WM3370HWA.ABWEPUS 4
WM3370HWA.ABWEPUS 321
WM3370HWA.ABWEPUS 38
WM4270HWA.ABWEPUS 1693
WM4270HWA.ABWEPUS 12721
WM8000HWA.ABWEEUS 1147
WM8000HWA.ABWEEUS 8584
WM8000HWA.ABWEEUS 29
WT5680HVA.ASSEPUS 1800
WT5680HVA.ASSEPUS 13519
DLEX4270W.ABWEEUS 987
DLEX4270W.ABWEEUS 7384
DLEX4270W.ABWEEUS 33
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
I don't know what you mean by a "dynamic macro", or how that differs from a "regular macro", but you might consider the following...

Code: 
Sub FirstValue()

Dim LastRow As Long
Dim i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A1").Offset(0, 1).EntireColumn.Insert
Range("A1").Value = "Model"
Range("A1").Offset(0, 1).Value = "Expected Cost"

'''''   TextToColumns to separate expected cost from model
Range("A2:A" & LastRow).Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
       
'''''   Delete all but the first of each model
For i = LastRow To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) Then Cells(i, 1).EntireRow.Delete
Next i

End Sub

If your data isn't in Column A you'll have to make the adjustments.

Cheers,

tonyyy
 
Upvote 0
Thank you very much for your reply and great help!
I have tried multiple ways to fit this code to my data, but I failed to do so.
Could you please help me with this?

Columns: AJ, AV, AW, AX, AY, AZ, BA, BB, BC, BD (There are few hidden columns, but I believe this doesn't really matter)

Thanks!

AJ AV AW AX AY AZ BA
[TABLE="width: 1437"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Model(Editable)[/TD]
[TD]Amount Per Unit(Editable)[/TD]
[TD]Expected QTY(Editable)[/TD]
[TD]Expected Cost(Editable)[/TD]
[TD]Apply Month(Editable) [/TD]
[TD]Effective Date(From)(Editable)[/TD]
[TD]Effective Date(To)(Editable)[/TD]
[TD]SPGM Tool Use
Flag[/TD]
[TD]Requestor[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]WM3370HWA.ABWEPUS[/TD]
[TD]107.[/TD]
[TD]0.[/TD]
[TD]4.[/TD]
[TD]201506[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WM3370HWA.ABWEPUS[/TD]
[TD]107.[/TD]
[TD]3.[/TD]
[TD]321.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WM3370HWA.ABWEPUS[/TD]
[TD]107.[/TD]
[TD]0.[/TD]
[TD]38.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WM4270HWA.ABWEPUS[/TD]
[TD]142.[/TD]
[TD]12.[/TD]
[TD]1,693.[/TD]
[TD]201506[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WM4270HWA.ABWEPUS[/TD]
[TD]142.[/TD]
[TD]90.[/TD]
[TD]12,721.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WM8000HWA.ABWEEUS[/TD]
[TD]232.[/TD]
[TD]5.[/TD]
[TD]1,147.[/TD]
[TD]201506[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WM8000HWA.ABWEEUS[/TD]
[TD]232.[/TD]
[TD]37.[/TD]
[TD]8,584.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WM8000HWA.ABWEEUS[/TD]
[TD]232.[/TD]
[TD]0.[/TD]
[TD]29.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WT5680HVA.ASSEPUS[/TD]
[TD]151.[/TD]
[TD]12.[/TD]
[TD]1,800.[/TD]
[TD]201506[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WT5680HVA.ASSEPUS[/TD]
[TD]151.[/TD]
[TD]90.[/TD]
[TD]13,519.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DLEX4270W.ABWEEUS[/TD]
[TD]142.[/TD]
[TD]7.[/TD]
[TD]987.[/TD]
[TD]201506[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DLEX4270W.ABWEEUS[/TD]
[TD]142.[/TD]
[TD]52.[/TD]
[TD]7,384.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DLEX4270W.ABWEEUS[/TD]
[TD]142.[/TD]
[TD]0.[/TD]
[TD]33.[/TD]
[TD]201507[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]US-NTSO-20150624-0088[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


tonyyy said:
I don't know what you mean by a "dynamic macro", or how that differs from a "regular macro", but you might consider the following...

Code: 
Sub FirstValue()

Dim LastRow As Long
Dim i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A1").Offset(0, 1).EntireColumn.Insert
Range("A1").Value = "Model"
Range("A1").Offset(0, 1).Value = "Expected Cost"

'''''   TextToColumns to separate expected cost from model
Range("A2:A" & LastRow).Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
       
'''''   Delete all but the first of each model
For i = LastRow To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) Then Cells(i, 1).EntireRow.Delete
Next i

End Sub

If your data isn't in Column A you'll have to make the adjustments.

Cheers,

tonyyy
Click to expand...
 
Upvote 0
In your original post it looked like your data was in a single column. In your most recent post it appears the model data is in a separate column from the amount. And, what appears to be column letters don't line up with the column headers - so it's confusing. Please use one of the recommended tools for posting your spreadsheet data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
354
Anthony47
A
R
  • Question Question
VBA - Insert pivot table into existing sheet on next available row
Replies
0
Views
519
ravecake
R
J
  • Solved
First Post: Macro / VBA to Move Entire Rows to New Sheets Based on Multiple Criteria From a List In A Separate Column
2
Replies
16
Views
4K
jholly1984
J
T
  • Question Question
Need help with finding corresponding value for max value in the other column
Replies
1
Views
623
123rickfear
1
F
  • Question Question
Help with Macro to Insert a New Column to the Left of Moving Column
Replies
4
Views
1K
Flash311
F

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top