Combine "unknown" columns per row to 2 columns

[kelzipan]

I'm not sure what to search or name this so apologies in advance.

I need to combine data as follows. fruit_attributes.jpg

APPLE RED FRUIT ROUND
BANANA FRUIT YELLOW

to

APPLE RED
APPLE FRUIT
APPLE ROUND
BANANA FRUIT
BANANA YELLOW

Can this be achieved with formulas or do I need a macro? I don't mind if the data is pasted into the same sheet. Basically each item has various attributes but I need to condense to just 2 columns of item and single attribute.

Please feel free to post an existing thread link. I tried some searches and found nothing.

Thanks!

[alansidman]

Here is a VBA solution

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[kelzipan]

Thanks for the quick reply. That worked! I'm not very skilled in VBA. Do you think something like that is possible with formulas or not really since it is a variable number of columns per item? I clicked on the star - thanks so much for the help!

I'm going to mark as solved (once I figure out how - I'm new to this forum!)

[alansidman]

Thanks for the Rep

[Kaper]

VBA seems to be best suited for this task, but if we want formulas - why not (will not be a piece of cake anyway).
I added header row and one dummy column (can be hidden or somewhere out of sight - say column Z etc.)
in this dummy column (A) in A2 I wrote 1 and in A3 (and copied down) formula:

Please Login or Register  to view this content.

In K2 (and copied down) formula returning first word:

Please Login or Register  to view this content.

but as we probably will copy this formula with some excess (too far down) it will be better to ad a condition checking:

Please Login or Register  to view this content.

and in L2 and copied down:

Please Login or Register  to view this content.

Or with added returning empty cell if column K is already empty:

Please Login or Register  to view this content.

See attached file

[kelzipan]

Thanks, Kaper. I also gave you a star. I feel like a teacher. I think this is what I had seen before and that's why I asked if there were a formula. I couldn't remember what the formula was.

I do think VBA is easier to "read" to see what is going on. I am not an expert with INDEX and MATCH functions. I do understand what the VBA is doing even if I can't write it exactly on my own yet. The INDEX and MATCH is a little more difficult for me to follow.

Thanks, all. Happy to be posting on this forum! :D

[Kaper]

Thanks for reputation and for marking thread solved.
INDEX & MATCH as well as VLOOKUP are between most used excel functions - see for instance recent thread in our WaterCooler: https://www.excelforum.com/the-water...ml#post4735368 ,
so probably it's now a good time to switch roles (from teacher become pupil :-P ) and learn more about them.

[kelzipan]

I like the power of VBA but I would like to figure out function equivalents (where possible) if I'm in a position where I can't use macros... Thanks for the help and the link!