Solved

How to take contents from a COLUMN of data and place as an array in a SINGLE CELL?

Posted on 2011-09-29
3
174 Views
Last Modified: 2012-05-12
Experts:

My data in Excel 2010 looks like this in a single column:

861.32,
861.22,
861.21,
860.5,
860.4,
860.2,
860.1,
860.0,
854.09,
854.06,
854.05,

I need it to be transposed INTO a SINGEL CELL and have it look like this:

861.32, 861.22, 861.21, 860.5, 860.4, 860.2, 860.1, 860.0, 854.09, 854.06, 854.05,

There are about 1300 codes in the list...FYI....

Thanks
0
Comment
Question by:MIKE
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36816963
Assuming the data is in column A starting in A1.

Put this in B2:

=A1 & A2

and this in B3

=B2&A3

then copy down from B3.

You can quickly do that by double clicking the bottom right of the cell (B3).
0
 
LVL 17

Author Comment

by:MIKE
ID: 36817015
This will work for what I'm trying to do....THANKS!
0
 
LVL 33

Expert Comment

by:Norie
ID: 36817096
Were you looking for code?
Dim arrVals
Dim strVals

arrVals = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value

strVals = Join(Application.Transpose(arrVals), "")

Range("B1") = strVals

Open in new window

By the way I just noticed there might be an extra comma at the end.

Do you want to keep that?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sum iF  based on a null cell 11 29
Excel Save As Status Box will not go away 6 19
Excel 2016 formulas 5 26
Add a range in an Excel graph 5 35
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now