Combining columns into row

[jetwatch]

Here is the data I have:

column 1 column 2 column 3
smith blue 4
smith green 3
smith red 4
jones blue 2
jones red 2
jones green 3
jones yellow 2
brown red 3
brown blue 3

etc.

I need it to be like this:

Column 1 column 2 column 3 column 4 column 5 column 6 etc.
smith blue 4 green 3 red 4
jones blue 2 red 2 green 3

Thank you for the assistance.

 

[markmzz]

You can try this too:

In F2 - use only Enter to enter the formula

=IFERROR(INDEX($A$2:$A$128,MATCH(0,INDEX(COUNTIF(F$1:F1,$A$2:$A$128)+($A$2:$A$128=""),),0)),"")

In G2 - use Ctrl+Shift+Enter to enter the formula

=IF($F2="","",IFERROR(INDEX($B$2:$C$128,SMALL(IF($A$2:$A$128=$F2,ROW($B$2:$B$128)-ROW($B$2)+1),COUNTIF($G$1:G$1,G$1)),MATCH(G$1,$B$1:$C$1,0)),""))

Markmzz

 

[VBA Geek]

@apo

 

[jetwatch]

Thanks to VBA Geek, apo, hiker95, markmzz and mirabeau.

At this point I'm going with the altered code (allowing for headings) posted by VBA Geek. I appreciate all your efforts to help me.

Thank you!
jet..

 

[hiker95]

jetwatch,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[hiker95]

jetwatch,

VBA Geek provided a formula solution that does work.

Did you even try my macro, or, the other macros that were posted?

 

[jetwatch]

jetwatch,

VBA Geek provided a formula solution that does work.

Did you even try my macro, or, the other macros that were posted?

Yes. "run time 429" error. Upon examination, here's the portion that was highlighted:

"With CreateObject ("Scripting.Dictionary")

 

[hiker95]

jetwatch,

Yes. "run time 429" error. Upon examination, here's the portion that was highlighted:

"With CreateObject ("Scripting.Dictionary")

Based on my reply #6, the screenshots did show that the macro worked on your attached file in your reply #3. And, the other macros also worked on the same file, with titles in row 1.

If there was an error, then there must be something different in your actual raw data workbook/worksheet?

If you would be interested in an updated macro, can we have another workbook/worksheets for testing?

A macro solution would be an automated resolution, instead of copying formulae, and, much faster.


I just figured it out - be back with instructions on how to add to the VBA Editor (select the correct VBA Tool Reference) so that the Scripting.Dictionary will work.

 

[hiker95]

jetwatch,

Open your workbook, and, follow the instructions in my reply #4, to add the macro in reply #6 into the VBA Editor.

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the macro, per the instructions in my reply #4.

 

[jetwatch]

jetwatch,

Open your workbook, and, follow the instructions in my reply #4, to add the macro in reply #6 into the VBA Editor.

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the macro, per the instructions in my reply #4.

I did as instructed, however I don't have Microsoft Scripting Runtime listed to check. Using Office for Mac.
Thanks for your diligence.

 

[hiker95]

jetwatch,

Using Office for Mac.

When asking for help in the future, I would suggest that you also state in your title that you are using a MAC.

Then you would get the proper support that you are looking for.


Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.