• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

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
0
jmac001
Asked:
jmac001
  • 3
  • 2
  • 2
2 Solutions
 
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 HensonIT & Database AssistantCommented:
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
 
jmac001Author Commented:
Alan,

Is there any way to get the blank lines out?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AlanConsultantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
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 HensonIT & Database AssistantCommented:
Formatting changes can only be applied after the macro option unless you copy and paste the udf results as values.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now