Merging 2 columns into a new one - but with specifics..

andybr

New Member
Joined
Sep 12, 2006
Messages
5
Hi all. I really am a novice with Excel and need some help please :)

I have a spread sheet full of data (its actually a turbo lister export from eBay). I really need to be able to copy certain data (and keep the original in place) from two columns so that that data lands into a third column but in specific places. I'm hoping and presuming this is relatively simple to conquer?

The attached picture should clarify my non techy explanation !!



Hope someone can help as this will make so much difference.

Cheers Andy :)
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

andybr

New Member
Joined
Sep 12, 2006
Messages
5
Reading a little further - I think its the CONCATENATE funtion - that I could do with help on ! - ta
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
hope this works
Code:
Sub test()
Dim r As Range
With CreateObject("VBScript.RegExp")
     .Pattern = "(DATA FROM)\s[AC]\d+"
     .Global = True
     For Each r In Range("b2",Range("b" & Rows.Count).End(xlUp))
         If .execute(r.Value).Count > 1 Then
         r.Offset(,-1).Value = r.Offset(,-1).Value & vbLf & .execute(r.Value)(0)
         r.Offset(,1).Value = r.Offset(,1).Value & vbLf & .execute(r.Value)(1)
         End If
     Next
End With
End Sub
 

andybr

New Member
Joined
Sep 12, 2006
Messages
5
Thanks, but couldnt get it to work (although thats probably more my doing)

It would be nice if I could simply tell it to:

replace text XXXXX with C,1 and replace text YYYYYY with A,1 and repeat down the rows en masse. !!

:) love excel
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Then you need to show us your desired result...
 

andybr

New Member
Joined
Sep 12, 2006
Messages
5
This shows it as if it was correct.
With the data from A and C transposed into the relevant points of B.



This is a sample and doesnt represent the final spreadsheet (which is actually about 160 columns long)

The unedited text in the B column will always be identical. A and C will just add a title and an image to the finished html

(Sorry if not explaining myself very well :oops:)

All help *greatly* appreciated.

Andy
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

How about
Code:
Sub test()
Dim r As Range
With CreateObject("VBScript.RegExp")
     For Each r In Range("b2",Range("b" & Rows.Count).End(xlUp))
         .Pattern = "(TITLE)\s\.+[^<]"
         If .test(r.Value) then r.Offset(,-1).Value = .execute(r.Value)(0)
         .Pattern = "(http)\.+[^" & Chr(34) & "]"
         If .test(r.Value) Then r.Offset(,1).Value = .execute(r.Value)(0)
     Next
End With
End Sub
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
The unedited text in the B column will always be identical. A and C will just add a title and an image to the finished html

based on the above condition, see if this will help.
try;
Rich (BB code):
Sub sample()
Dim i As Long
For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
Cells(i, "b") = "<P align=center><FONT size=3><FONT size=6></FONT><FONT size=6>" & Cells(i, "a") & "
</FONT>
</FONT></P>"
Next
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OOps!
my code is doing opposite.

extracting the title value to A and other to C from col.B....
 

andybr

New Member
Joined
Sep 12, 2006
Messages
5
I really appreciate all your help,

I will get back and let you know how I get on.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,059
Messages
5,545,759
Members
410,704
Latest member
Cobber2008
Top