asked on

Function, VBA, Index/Match, not sure.

Hi Experts,

Tried to research the solution that I need but not sure what to look for. I looked at index/match but that doesn't seem to work. I would like to take the data from columns R-AA on the OrigFormat tab of the attached workbook and return it to column F of the Selection tab with a hard return for each column menu item. Is this possible to do?
ee-2015-Food-Vendor-v1.xlsm

Alan

Bit messy, but for a one-off, would this work (entered into Selection!F2):

=OrigFormat!R2&CHAR(10)&OrigFormat!S2&CHAR(10)&OrigFormat!T2&CHAR(10)&OrigFormat!U2&CHAR(10)&OrigFormat!V2&CHAR(10)&OrigFormat!W2&CHAR(10)&OrigFormat!X2&CHAR(10)&OrigFormat!Y2&CHAR(10)&OrigFormat!Z2&CHAR(10)&OrigFormat!AA2

Copy down as required.

See attached Version 2 of your file.

ee-2015-Food-Vendor-v2.xlsm

Alan.

Rob Henson

A fairly simple VBA or User Defined Function would do this for you.

Not sure on proper syntax but along the lines of:

TextString = Range(R2).Value & CHR(10)
For Each Cell in Range(S2:AA2)
TextString = TextString & Cell.Value & CHR(10)
Next Cell

Thanks
Rob H

jmac001

ASKER

Alan,

Is there any way to get the blank lines out?

SOLUTION

Alan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmac001

ASKER

Thank you both for solution both they both work really well and I would be able to duplicate for use in different spreadsheet. Alan, I chose Rob's solution as the best because it will allow me to work with the data. I won't have to paste as values so that I can make formatting changes to some of the menu line items.

Rob Henson

Formatting changes can only be applied after the macro option unless you copy and paste the udf results as values.