Multiple inputs and one output 2

[westex96]

Hi,

I am having a hard time to create a macro that will format the following in excel. I have 20000 inputs like this and vary in quantity of sector and MAIO.

SECTOR MAIO
TX00021 3
TX00021 2
TX00021 1
TX00021 0
TX00022 0
TX00022 2
TX00022 1
TX00023 2
TX00023 1
TX00023 0

TO the following one output sector cell and one MAIO cell.

SECTOR MAIO
TX00021 3 2 1 0
TX00022 0 2 1
TX00023 2 1 0

Any help will be greatly appreciated.

Thanks!

Ray

 

[AnotherHiggins]

Try this:

Sub test()
    Dim intLstRow As Integer
    intLstRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

    For i = intLstRow To 2 Step -1
        If Range("A" & i) = Range("A" & i - 1) Then
            Range("B" & i - 1) = Range("B" & i - 1) & " " & Range("B" & i)
            Range(i & ":" & i).Delete
        End If
    Next i
End Sub

When deleting rows, it is easier to loop from the bottom up.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[westex96]

WOW! Thank you so much anotherhiggins! It work perfect. Bottom up approach is a very nice touch!

Thank you! Thank you! Thank you!


Ray

 

[Gavona]

Thank you! Thank you! Thank you!

Sounds like you should award a star! On the bottom of his post click on:

[blue]* Thank anotherhiggins
for this valuable post![/blue]


Gavin

 

[fumei]

Yes, westex96, any time someone's response gives you a thank you, thank you, thank you feeling, it is good to express it. Not only does giving a star pass that thank you to the person who posted, but it also marks a thread as possibly valuable to other people.

BTW: regarding buttom-up, it is not just easier, but in many cases it is crucial to work that way.

faq219-2884

Gerry
My paintings and sculpture

 

[AnotherHiggins]

Gerry,

You can make it work from top-down, but it is needlessly complex.

How do I know? Because before I came to Tek-Tips, where I learned that "Step - 1" was an option, I had brute-forced my way through top-down. In fact, I think I learned about bottom-up after I posted an answer using logic like below only to be corrected shown the light by another member.

Example of the hard way:

Sub test1()
    Dim intLstRow As Integer
    intLstRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
    i = 2

    Do While i < intLstRow
        If Range("A" & i) = Range("A" & i + 1) Then
            Range("B" & i) = Range("B" & i) & " " & Range("B" & i + 1)
            Range(i + 1 & ":" & i + 1).Delete
            intLstRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
        Else
            i = i + 1
        End If
    Loop
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[westex96]

Hi Guys,

I do believe I clicked the star for John. Now that I am understanding this a bit, can you answer another question?What if the B column is not an integer and you would like the same output? Ho do I call the character instead of the integer?

Sector Neighbor
HX00081 HX00082
HX00081 HX00083
HX00081 HX50801
HX00081 HX52067
HX00081 HX52068
HX00081 HX52069
HX00082 HX00081
HX00082 HX00083
HX00082 HX50802
HX00082 HX50801
HX00082 HX06222
HX00082 HX52068
HX00082 HX06221
HX00082 HX50781
HX00082 HX04291
HX00082 HX50803
HX00082 HX06652
HX00082 HX04293
HX00082 HX01151
HX00083 HX00081
HX00083 HX00082
HX00083 HX50781
HX00083 HX01891
HX00083 HX50783

Thanks!

Ray

 

[SkipVought]

Ray,

"I do believe I clicked the star for John."

You might recheck the PROCESS that you failed to complete.

"Ho do I call the character instead of the integer?"

John's process does not "call ...integer".

Did you actually TRY running it?




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[westex96]

I stand corrected I hit the star but did not complete the next step. I did now. Yes I did run the macro and it works great. The only thing is when I replace the B column with a HX0001 then I get an error on this line.

intLstRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

Thanks!

 

[SkipVought]

My result running John's code on your latest data...
[tt]
Sector Neighbor
HX00081 HX00082 HX00083 HX50801 HX52067 HX52068 HX52069
HX00082 HX00081 HX00083 HX50802 HX50801 HX06222 HX52068 HX06221 HX50781 HX04291 HX50803 HX06652 HX04293 HX01151
HX00083 HX00081 HX00082 HX50781 HX01891 HX50783
[/tt]
Prior to running John's code, did you have an OBJECT other than the SHEET containing this data selected -- like a SHAPE or a CHART or a CONTROL?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[westex96]

intresting...No I didn't. I am glad it worked for you. I will try again in a bit. Thank you so much for looking into this!

Ray.

 

[PHV]

I get an error on this line
Which error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

anotherhiggins,

Hi John, my comment was a general statement in that for "many cases" it is crucial to work bottom-up. Say...working through a Collection. Say, working through rows in a Word table. If there is an instruction that deletes rows, or if it uses the index number of the row, then working bottom-up is crucial.

faq219-2884

Gerry
My paintings and sculpture