Define Name or Worksheet to use?

**calvinle** · 09-16-2014, 08:34 PM

Hi everyone,

I will have a question so I wonder whch one will you use.
I have thousand categories, and each category has different % of buyers.
Example:
Dog:
10% Asian
20% Russia
70% American

Cat:
50% Asian
50% Africa

So on.

Now I would like to know if it was you, will you use the define name, or different worksheet to group them?

Like if the user choose cat, the list of buyer will appear and their %.

Which method will take less memory but more efficiency to search, ect.
Or any other method you can suggest?

Thanks.

**Richard Buttrey** · 09-16-2014, 08:47 PM

Hi,

Use a 3 column table with

Category
%
Country

entered on each line.

**calvinle** · 09-16-2014, 09:13 PM

The country varies, so how can i make it 3 columns?

**SCLai** · 09-16-2014, 10:46 PM

Hi,

Perhaps Categories as rows and Countries as columns to form a table? Categories with no % in Country can be left as 0%... That would be my opinion if I got your question right.

SC

**calvinle** · 09-16-2014, 11:20 PM

That's not a bad idea, but if there is thousand category, the list will go long horizontally in the worksheet? We will then reach ZZ?

**SCLai** · 09-17-2014, 02:07 AM

That is true... So perhaps:
1. Place the one with less entries as the Column
2. If there are still too many, limit the number of columns per sheet and split the data over a few sheets? You can do lookup for a column over multiple sheets with a single formula for enquiry in this case.
Just my 2 cents worth of thoughts.

SC

**Richard Buttrey** · 09-17-2014, 04:47 AM

Originally Posted by calvinle

The country varies, so how can i make it 3 columns?

You put the name of the country in the 3rd column, i.e. column C values will vary.
Similarly the Category (dog, cat, etc) will vary in column A and of course the % in column B.

Once you've done this a Pivot Table analysis is just a few mouse clicks away.

Define Name or Worksheet to use?

LinkBack

Thread Tools

Rate This Thread

Display

Define Name or Worksheet to use?

Re: Define Name or Worksheet to use?

Re: Define Name or Worksheet to use?

Re: Define Name or Worksheet to use?

Re: Define Name or Worksheet to use?

Re: Define Name or Worksheet to use?

Re: Define Name or Worksheet to use?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to define a range in another worksheet ?

[SOLVED] Define global worksheet name for a userform

Define a Workbook/WorkSheet Name by a String Value

[SOLVED] How to take the worksheet name as a variable(Label/Define) in a fo

[SOLVED] how can i define initial worksheet

Bookmarks

Bookmarks

Posting Permissions