Excel Multiple inputs to one output

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

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.

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

Any help will be greatly appreciated.

Thanks!

Ray

 

[AnotherHiggins]

Please repost your question in forum707.

Also, do you want
[tab]3 2 1 0
in a single cell with spaces between the numbers, or do you want each of the numbers in a different column?

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

 

[Gavona]

With your data in Columns A and B, Headings in row 1.

Put this in cell C2
=IF(A2=A1,"DeleteRow",IF($A3=$A2,$B3,""))
this in Cell D2
=IF($A4=$A2,$B4,"")
and this in cell E2
=IF($A5=$A2,$B5,"")

Copy down your 20,000 rows.
Copy
PasteSpecial,Values

Now sort by column C
Delete the rows containing "DeleteRow"
Re-sort if necessary.

Use the macro recorder if you want to automate and post any VBA queries in forum707.


Gavin

 

[westex96]

Thank you Anotherhiggins and Gavona.

Anotherhiggins I posted to the below thread and I read a bit of your FAQ 182 doc.

Gavona your method works well. Unfortunantly I forgot to state that the repeated input varies in length up to 75 repeats so I have to paste 75 times for this methond to work and the concatenate 75 times for a single cell. The method does work well though and very smart approach. : )

Thank you for your time!

Ray

 

[Gavona]

Ray, you could adapt the formula approach readily, now we know you want results in one cell. Start at the bottom of the input list. Untested but something like
In C1
=IF(A1=A2,C2&B1,B1)
Copy to C2
Leave D1 blank
in D2
=if(A2=A1,"DeleteRow","")
Select C2 & D2
Filldown this is a single click)
Copy, PasteSpecial,Values
.......


Gavin