Create a new set of data from 2 data sets

[CS Chia]

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

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

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

Merged data - 2 columns (Country and Stock list
Country Stock Lis
Malaysia Appl
Malaysia Orang
Malaysia Pea
Malaysia Melo
Malaysia Carro
Singapore Appl
Singapore Orang
Singapore Pea
Singapore Melo
Singapore Carro
China Appl
China Orang
China Pea
China Melo
China Carro
Taiwan Appl
Taiwan Orang
Taiwan Pea
Taiwan Melo
Taiwan Carro

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

Many thanks in advance

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[CS Chia]

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 no
fixed.
I have thought of using text CONCATENATE formula but the dynamic of th
data makes it challenging to formulate one that can be dynamicall
used.

Thanks again for your help in this

 

[Ron Rosenfeld]

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.

 

[GS]

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

 

[CS Chia]

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 stoc
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 fo
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 othe
options such as Macro or Access. My final end user are not technicall
strong and my organization does not support the use of Macro

 

[GS]

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.

What you show here are just 2 lists! Are you saying there's more? If so
then as Ron suggests.., any solution offered will work with what you
show us but no guarantee it will work with your real data. Better for
you and us if you show real data. Even better, provide a link to your
file and make sure it contains a sample of the results you expect!

My final end user are not technically strong

All the more reason to automate the process.

and my organization does not support the use of Macro.

In this case it's going to be necessary for your end users to upgrade
their skills so they'll know how to make adjustments for the dynamic
changes in your data!

--
Garry

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

 

[Ron Rosenfeld]

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.

Here is a solution FOR THE PROBLEM YOU POSE. Whether it will work on your real data I have no idea, but I will leave it to you to adapt.

This assumes that your list of countries is NOT how you have presented it, but rather is in a column with a header and the list of countries proceeding row by row.
It makes the same assumption for Stock except that is in column B, again starting at B1.

Furthermore, your output will be in columns G & H, again starting in row 2.

You can change the columns (or sheet), but these two columns must be contiguous or you must change the formula.
If you change the starting row numbers, you will need to change some of the formula parameters.

So, we have:

A1:A5:
Country
Malaysia
Singapore
China
Taiwain

B1:B6
Stock
Apple
Orange
Pear
Melon
Carrot

For flexibility, I have used dynamic named ranges:

Country Refers to: =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1)
Stock Refers to: =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1)

You can change the sheet name if needed. The list must be contiguous (no blanks) and have nothing else in the columns other than what I have shown. But if you add Counties or Stock, the named range will expand to include the additions.

G1: Country
H1: StockList

G2: =IFERROR(INDEX(Country,INT((ROW()-2)/COUNTA(Stock)+1)),"")
H2: =IF(G2<>"",INDEX(Stock,MOD(ROW()-2,COUNTA(Stock))+1),"")

Then select G2:H2 and fill down as far as required (until you start to see blanks for output, or a minumum of =counta(country)*counta(stock) rows.

 

[GS]

In case anyone's interested in doing similar in XL2003...

In G2: Replace
=IFERROR(INDEX(Country,INT((ROW()-2)/COUNTA(Stock)+1)),"")

with


=IF(ISERROR(INDEX(Country,INT((ROW()-2)/COUNTA(Stock)+1))),"",INDEX(Country,INT((ROW()-2)/COUNTA(Stock)+1)))

--
Garry

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

 

[Ron Rosenfeld]

In case anyone's interested in doing similar in XL2003...

In G2: Replace
=IFERROR(INDEX(Country,INT((ROW()-2)/COUNTA(Stock)+1)),"")

with


=IF(ISERROR(INDEX(Country,INT((ROW()-2)/COUNTA(Stock)+1))),"",INDEX(Country,INT((ROW()-2)/COUNTA(Stock)+1)))

Thanks, Garry.
I don't even think about XL2003 or earlier versions anymore, unless it's on the Microsoft Answers forum where the version is often cited.

 

[GS]

Thanks, Garry.

I don't even think about XL2003 or earlier versions anymore, unless
it's on the Microsoft Answers forum where the version is often cited.

What amazes me at this date is how many folks refuse to move forward! I
know for sure a dislike for the Ribbon is the main reason, even though
the 'Classic' Menubar menus can be put on the Addins tab.

--
Garry

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

 

[Ron Rosenfeld]

What amazes me at this date is how many folks refuse to move forward! I
know for sure a dislike for the Ribbon is the main reason, even though
the 'Classic' Menubar menus can be put on the Addins tab.

But why upgrade if the version you have meets your requirements? That's the reason I've been staying at 2007.
Of course, the cloud versions are something I would be philosophically opposed to even if I had decent internet service here in the sticks.

The cloud versions bring me back to a long time ago, when we had a central computer and multiple dumb terminals. And if the central computer went down, the dumb terminals were useless.
Seems we're coming full circel.

 

[GS]

But why upgrade if the version you have meets your requirements?
That's the reason I've been staying at 2007. Of course, the cloud
versions are something I would be philosophically opposed to even if
I had decent internet service here in the sticks.

This is why I haven't install my 2010 yet. Also why I continue to
develop in 2003 since my apps use the Addins tabs rather than custom
tabs on the Ribbon. That said, though, I'm close to dropping support
for pre-2007 but will continue to offer (as an alternative) my
stand-alone VB6.EXEs (which use the Farpoint Spread.ocx spreadsheet
ActiveX component) for those who don't want to (or won't) work with the
Ribbon.

The cloud versions bring me back to a long time ago, when we had a
central computer and multiple dumb terminals. And if the central
computer went down, the dumb terminals were useless. Seems we're
coming full circel.

I agree with you on this!

--
Garry

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