How to Combine Data from Several Columns?

[hce]

Hi Everyone

I have attached a file which contains what I need to resolve
Basically, I need to combine the data in several columns together int
one column. Is there a way I can do it with formulas or vb code?
would really appreciate if any experts out there can help me.

Thanks and Regards
Kelvi

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46105

 

[Norman Harker]

Hi Kelvin!

Probably the answer will be something like:

=A1&" "&B1&" "&C1

But it depends upon the data. For example if A1 is a date you might
use:

=TEXT(A1,"dd-mmm-yyyy")&" " etc.

With formulas you might get:

=A1&" "&B1*(1-C1)&" "&D1

Send details of the data and formulas and preferred formats and I'm
sure we can tailor a solution.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JE McGimpsey]

For various reasons, including potential macro virii, I did not download
your workbook. You might, however, find macros at this site helpful.

http://www.mcgimpsey.com/excel/mergedata.html

 

[JulieD]

Hi Norman

i had a look at the file and basically the structure is

A B C D E
F
1 .5 A Q
2 .7 B
3 .3 C O Q

and what he wants is in column F & G

F G
1 .5 A
2 .7 B
3 .3 C
4 .5 Q
5 .3 O
6 .3 Q

etc where the values in A are duplicated firstly where there's a match in
column B, then C, then D etc

I guess this could be done by writing a loop code that sees if there is an
entry in A,checks if there's an entry in B and writes it to F etc .. but my
code is never as good as others in this forum so i though i would leave it
.... but i thought i would detail the contents of the attachment so others
don't have to open it up

Cheers
JulieD

Hi Kelvin!

Probably the answer will be something like:

=A1&" "&B1&" "&C1

But it depends upon the data. For example if A1 is a date you might
use:

=TEXT(A1,"dd-mmm-yyyy")&" " etc.

With formulas you might get:

=A1&" "&B1*(1-C1)&" "&D1

Send details of the data and formulas and preferred formats and I'm
sure we can tailor a solution.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Julie
one way (with formulas)
1. put the following in F1
=IF(A1<>"",A1,"")
2. put the following in G1 (array entered with CTRL+SHIFT+ENTER)
=IF(A1<>"",INDEX(B1:F1,MIN(WENN(B1:F1<>"",COLUMN(B1:F1),20))),"")
copy both formulas down

 

[Norman Harker]

Hi JulieD!

Aaaah!

If your BCDE entries are in alpha order, I'd be inclined to insert 3
empty rows between each row of data. Then the formulas are fairly
straightforword. After getting numeric and alphas side by side, delete
empty rows (including those that are fotmula returned "") and sort.

If the data isn't in alpha order life with a formula solution becomes
much more difficult.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Frank and JulieD!

For WENN substitute IF

Frank, your brain is working faster on the solution than it is at
translation <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Norman
I managed to replace the semicolon with the coma but the rest......
<vbg>
I only wonder what time it is for your down-under in Australia??

 

[AlfD]

If you want a simple (extendable) VBA loop approach, try this:

Sub moveletters()
counter = 2 'Will start new list in row 2
With Range("A1:I40") ' Using A1 ensures you don't have to worr
about cells(i,j) being a relative - to A1, in this case - address

For j = 2 To 5 'Columns B to E
For i = 2 To 14 ' Rows 2 to 14
If .Cells(i, j) <> "" Then
.Cells(counter, 6) = .Cells(i, j) 'Letters in co
F
.Cells(counter, 7) = .Cells(i, 1) 'Numbers in co
G
counter = counter + 1
End If
Next i
Next j
End With
End Sub

Good luck!

Al

 

[Norman Harker]

Hi Frank!

It's 2:30 AM. Still a trifle hot here which means this is the best
time of the day to be working.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Frank!

It's 2:30 AM. Still a trifle hot here which means this is the best
time of the day to be working.

Hi Norman
current temperature in Frankfurt: -5 C -> time to quit working