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.