Sap 1 to Sap 2 card transfer

[makinmomb]

100.000.01	GEMMA 40 X 40 = KARNAK BEIGE P GRANITO	FLOOR GEMMA	KARNAK BEIGE	GEMMA	007A	0	remove
100.000.02	GEMMA 40 X 40 = KARNAK GREY P GRANITO	TILE GEMMA	KARNAK GREY	GEMMA	007A	0	remove
100.000.03	GEMMA 40 X 40 = MYKONOS GREYP GRANITO	FLOOR TILE GEMMA	MYKONOS GREY	GEMMA	007A	0	remove
100.000.04	GEMMA 50 X 50 = ARIZONA BEIGE		ARIZONA BEIGE	GEMMA	005A	0	remove
100.000.05	GEMMA 50 X 50 = VERANDA BRICK		VERANDA BRICK	GEMMA	005A	0	remove
100.000.06	GEMMA 50 X 50 = VERANDA BRICK GEOMETRIC	GEMMA	VERANDA BRICK	GEMMA	005A	0	remove
100.000.07	GEMMA 25 X 35 = BOMBAY GREEN		BOMBAY GREEN	GEMMA	016A	0	remove
100.000.08	GEMMA 30 X 44 = ISCANDARONA BROWN DARK		ISCANDARONA BROW	GEMMA	011A	0	remove
100.000.09	GEMMA 20 X 20 = SOLANGE IVORY		SOLANGE	GEMMA	022A	1000.00
100.000.10	CLOSED CARD			GEMMA	PC	0	remove
100.000.11	CLOSED CARD			GEMMA	PC	0	remove
100.000.12	GEMMA 20X30 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	020H	0	remove
100.000.13	GEMMA 20X30 = FRUTTI BRICK		FRUTTI BRICK	GEMMA	020H	0	remove
100.000.14	GEMMA LISTELO 10 X 20 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.15	GEMMA DECOR 20X30 = FRUTTI BEIGE	GEMMA	FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.16	GEMMA 30 X 30 = ARIZONA MARRON		ARIZONA MARRON	GEMMA	015A	2000.00

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

100.000.01	GEMMA 40 X 40 = KARNAK BEIGE P GRANITO	FLOOR GEMMA	KARNAK BEIGE	GEMMA	007A	0	remove
100.000.02	GEMMA 40 X 40 = KARNAK GREY P GRANITO	TILE GEMMA	KARNAK GREY	GEMMA	007A	0	remove
100.000.03	GEMMA 40 X 40 = MYKONOS GREYP GRANITO	FLOOR TILE GEMMA	MYKONOS GREY	GEMMA	007A	0	remove
100.000.04	GEMMA 50 X 50 = ARIZONA BEIGE		ARIZONA BEIGE	GEMMA	005A	0	remove
100.000.05	GEMMA 50 X 50 = VERANDA BRICK		VERANDA BRICK	GEMMA	005A	0	remove
100.000.06	GEMMA 50 X 50 = VERANDA BRICK GEOMETRIC	GEMMA	VERANDA BRICK	GEMMA	005A	0	remove
100.000.07	GEMMA 25 X 35 = BOMBAY GREEN		BOMBAY GREEN	GEMMA	016A	0	remove
100.000.08	GEMMA 30 X 44 = ISCANDARONA BROWN DARK		ISCANDARONA BROW	GEMMA	011A	0	remove
100.000.09	GEMMA 20 X 20 = SOLANGE IVORY		SOLANGE	GEMMA	022A	1000.00
100.000.10	CLOSED CARD			GEMMA	PC	0	remove
100.000.11	CLOSED CARD			GEMMA	PC	0	remove
100.000.12	GEMMA 20X30 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	020H	0	remove
100.000.13	GEMMA 20X30 = FRUTTI BRICK		FRUTTI BRICK	GEMMA	020H	0	remove
100.000.14	GEMMA LISTELO 10 X 20 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.15	GEMMA DECOR 20X30 = FRUTTI BEIGE	GEMMA	FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.16	GEMMA 30 X 30 = ARIZONA MARRON		ARIZONA MARRON	GEMMA	015A	2000.00

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

 

[Rick Rothstein]

no formula manually written

Give this macro a try...

Sub DeleteRowsWithRemoveInColumnH()
  Columns("H").SpecialCells(xlConstants).EntireRow.Delete
End Sub

 

[makinmomb]

This is working perfect rick

Except 100.000.09 then needs to be 100.000.01 and the next code 100.000.02

If it does that for 100.000.99 that is ninety nine time than the next code will be
100.002.01

 

[makinmomb]

You half answer will do too as I can write 100.000.01 and drag

But now sure if the full automation is possible ?

 

[makinmomb]

I changed my mind Rick

I am more than happy with the formula

It does not need any further modifications

sweet and can handle gigantic data

 

[Rick Rothstein]

I changed my mind Rick

I am more than happy with the formula

It does not need any further modifications

I can modify it easily enough, I just need to understand your numbering system first (see below).

Except 100.000.09 then needs to be 100.000.01 and the next code 100.000.02

If it does that for 100.000.99 that is ninety nine time than the next code will be
100.002.01

Why wouldn't 100.001.01 follow 100.000.99?

Also, why not 100.001.00 instead of 100.001.01?

 

[makinmomb]

You are correct it needs to be 100.001.00 after 100.000.99

 

[makinmomb]

Then when 100.001.01 and 100.001.02
once it reaches 100.001.99 it will be 100.002.00

I have this going upto 100.007.00 and so

 

[Rick Rothstein]

You are correct it needs to be 100.001.00 after 100.000.99

Then when 100.001.01 and 100.001.02
once it reaches 100.001.99 it will be 100.002.00

I have this going upto 100.007.00 and so

Give this macro a try...

Sub DeleteRowsWithRemoveInColumnH()
  Dim LastRow As Long
  Columns("H").SpecialCells(xlConstants).EntireRow.Delete
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A1:A" & LastRow) = Evaluate("IF(ROW(),TEXT(10000000+ROW(A1:A" & LastRow & "),""000\.000\.00""))")
End Sub

 

[makinmomb]

super hot

I can actually see the portion but at minute seconds

When the first card is 100.000.09 and takes 100.000.01

Just exact

Thanks rick not to forget it was you who introduced me
macros in December 2013

 

[Rick Rothstein]

I can actually see the portion but at minute seconds

I forgot to hide the manipulations from view. The following should be more visually appealing...

Sub DeleteRowsWithRemoveInColumnH()
  Dim LastRow As Long
  [COLOR=#0000FF]Application.ScreenUpdating = False[/COLOR]
  Columns("H").SpecialCells(xlConstants).EntireRow.Delete
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A1:A" & LastRow) = Evaluate("IF(ROW(),TEXT(10000000+ROW(A1:A" & LastRow & "),""000\.000\.00""))")
  [COLOR=#0000FF]Application.ScreenUpdating = True[/COLOR]
End Sub