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
jmac001Asked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
See attached file with macro and User Defined Function options.

UDF is on original Selection sheet, entered as =Collate(OrigFormat!R2:AA2) and then copied down. This works but you have to enter each cell and force a recalculate; select cell, press F2 and press enter. Tedious and I don't know how to get round this one.

I ran the macro option on copy sheet, Selection(2). Clear contents of column F and then run the Macro by pressing Alt & F8 and then choose Collate2 and click Run.

Both assume that the two sets of data match row for row on both sheets.

Thanks
Rob H
ee-2015-Food-Vendor-v1.xlsm
0
 
AlanConsultantCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
jmac001Author Commented:
Alan,

Is there any way to get the blank lines out?
0
 
AlanConnect With a Mentor ConsultantCommented:
Hi,

You can wrap the formula in CLEAN:

=CLEAN(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)

See attached Version 3.

Alan.
ee-2015-Food-Vendor-v3.xlsm
0
 
jmac001Author Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
Formatting changes can only be applied after the macro option unless you copy and paste the udf results as values.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.