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
Malaysia Pear
Malaysia Melon
Malaysia Carrot
Singapore Apple
Singapore Orange
Singapore Pear
Singapore Melon
Singapore Carrot
China Apple
China Orange
China Pear
China Melon
China Carrot
Taiwan Apple
Taiwan Orange
Taiwan Pear
Taiwan Melon
Taiwan Carrot

Both data set are dynamic, i.e. the lists are likely to change over time.
Can this be done without using VB script?

Many thanks in advance.

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
==================================================

Oops, forgot the column headers:

===============================
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, 1 To 2)

k = 1
vTbl(k, 1) = "Country"
vTbl(k, 2) = "Stock List"
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
========================================

Hi Ron,

Thanks for your help on this.
Due to organization policy, I have to avoid using scripting or macro.

I was hoping some kind of Excel formula could help in this.
I guess, this may be tough.

Another major constraint I have is that the list of items are not fixed.
I have thought of using text CONCATENATE formula but the dynamic of the data makes it challenging to formulate one that can be dynamically used.

Thanks again for your help in this.

On Tue, 27 Aug 2013 03:09:09 +0100, CS Chia wrote:


Hi Ron,

Thanks for your help on this.
Due to organization policy, I have to avoid using scripting or macro.

I was hoping some kind of Excel formula could help in this.
I guess, this may be tough.

Another major constraint I have is that the list of items are not
fixed.
I have thought of using text CONCATENATE formula but the dynamic of the
data makes it challenging to formulate one that can be dynamically
used.

Thanks again for your help in this.

With Data Sets currently consisting of strings with a colon delimited header and comma delimited data, I would suggest you work with your IT department to provide a more easily manageable format for the Data Set as a first step.
That would be a first step.

With Data Sets currently consisting of strings with a colon delimited
header and comma delimited data, I would suggest you work with your
IT department to provide a more easily manageable format for the Data
Set as a first step. That would be a first step.

It's probably a good idea (IMO) if they follow basic data store
rules/principles instead of making up their own as they go!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi,

The data is stored in columns:
I have 2 tab for data storage - Country tab and stock tab.
Essentially, there are 2 different tables storing country data and stock data.
It is a massive data that I need to merge here.
I am using the country name and stock ID as the primary key for referencing.
I have simplified the data for easy reference here.

Just need some creative use of Excel formula to merge the data.

I would like to explore Excel formula first before exploring other options such as Macro or Access. My final end user are not technically strong and my organization does not support the use of Macro.