On Mon, 26 Aug 2013 03:03:16 +0100, CS Chia wrote:


I have 2 list of data and I need a formula that can create a new data
set by merging the 2 list of data.

For example:
Data set 1:
Country Name: Malaysia, Singapore, China, Taiwan

Data set 2:
Stock List: Apple, Orange, Pear, Melon, Carrot

Merged data - 2 columns (Country and Stock list)
Country Stock List
Malaysia Apple
Malaysia Orange

You may have simplified your data to the point where a solution that works for what you've shown will not work on the real data.

You show your data sets as two strings with a colon delimited header and comma delimited data.
With your data sets as text strings, and the merged data in columns, it would be extremely difficult to do this without VBA.

A macro can easily accomplish the task, however.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

===========================================
Option Explicit
Sub CreateTable()
Dim vTbl() As Variant
Dim aCountryList As Variant
Dim aStockList As Variant


'DataSets could also be set to the two cells in which they occur
' and the splitting would have to be done differently
Const DataSet1 = "Country Name: Malaysia, Singapore, China, Taiwan"
Const DataSet2 = "Stock List: Apple, Orange, Pear, Melon, Carrot"

Dim i As Long, j As Long, k As Long

Dim rMergedData As Range

aCountryList = Split(Replace(Mid(DataSet1, InStr(DataSet1, ":") + 1), " ", ""), ",")
aStockList = Split(Replace(Mid(DataSet2, InStr(DataSet2, ":") + 1), " ", ""), ",")

ReDim vTbl(1 To (UBound(aCountryList) + 1) * (UBound(aStockList) + 1), 1 To 2)

For i = 1 To UBound(aCountryList) + 1
For j = 1 To UBound(aStockList) + 1
k = k + 1
vTbl(k, 1) = aCountryList(i - 1)
vTbl(k, 2) = aStockList(j - 1)
Next j
Next i

Set rMergedData = Range("A1").Resize(rowsize:=UBound(vTbl, 1), columnsize:=2)
rMergedData.EntireColumn.Clear
rMergedData = vTbl
rMergedData.EntireColumn.AutoFit
End Sub
==================================================