Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Create a new set of data from 2 data sets
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. Last edited by CS Chia : August 26th 13 at 03:07 AM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
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 ================================================== |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
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 ======================================== |
#4
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
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 |
#7
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
On Wed, 28 Aug 2013 03:06:34 +0100, CS Chia wrote:
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
On Wed, 28 Aug 2013 10:30:53 -0400, GS wrote:
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 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. |
#12
|
|||
|
|||
Hi Gary and RON,
Amazing Amazing.... Many many thanks to both of you. This is exactly what I need. Really appreciate the helps offered. Once again, many thanks for the help. CS |
#13
|
|||
|
|||
Hi Ron and Gary,
Many thanks for the help here. It is very helpful and it works as required. Really appreciates all the help offered. Cheers, CS |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
On Wed, 28 Aug 2013 23:29:07 -0400, GS wrote:
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. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create a new set of data from 2 data sets
On Wed, 28 Aug 2013 23:29:07 -0400, GS wrote:
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create 3rd data set off comparison of 2 other sets | Excel Worksheet Functions | |||
using excel to create data sets | Excel Discussion (Misc queries) | |||
how do i create a chart with 4 sets of data? | Charts and Charting in Excel | |||
how to create a line-chart with 2 sets of data who has different x | Charts and Charting in Excel | |||
Create programmatically list of combinations for choices from data sets in excel vba | Excel Programming |