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

[thlee1122]

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

 

[tonyyy]

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...

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

 

[thlee1122]

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

Model(Editable)	Amount Per Unit(Editable)	Expected QTY(Editable)	Expected Cost(Editable)	Apply Month(Editable)	Effective Date(From)(Editable)	Effective Date(To)(Editable)	SPGM Tool Use Flag	Requestor	Quantity
WM3370HWA.ABWEPUS	107.	0.	4.	201506			US-NTSO-20150624-0088
WM3370HWA.ABWEPUS	107.	3.	321.	201507			US-NTSO-20150624-0088
WM3370HWA.ABWEPUS	107.	0.	38.	201507			US-NTSO-20150624-0088
WM4270HWA.ABWEPUS	142.	12.	1,693.	201506			US-NTSO-20150624-0088
WM4270HWA.ABWEPUS	142.	90.	12,721.	201507			US-NTSO-20150624-0088
WM8000HWA.ABWEEUS	232.	5.	1,147.	201506			US-NTSO-20150624-0088
WM8000HWA.ABWEEUS	232.	37.	8,584.	201507			US-NTSO-20150624-0088
WM8000HWA.ABWEEUS	232.	0.	29.	201507			US-NTSO-20150624-0088
WT5680HVA.ASSEPUS	151.	12.	1,800.	201506			US-NTSO-20150624-0088
WT5680HVA.ASSEPUS	151.	90.	13,519.	201507			US-NTSO-20150624-0088
DLEX4270W.ABWEEUS	142.	7.	987.	201506			US-NTSO-20150624-0088
DLEX4270W.ABWEEUS	142.	52.	7,384.	201507			US-NTSO-20150624-0088
DLEX4270W.ABWEEUS	142.	0.	33.	201507			US-NTSO-20150624-0088

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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...

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

 

[tonyyy]

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.