• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Grid mapping

T

tdd903

New Member
I have a gridded space of 50 by 50 that stores product by coordinates (Media, Books, 10-25; Media, Music 12-2; Tools, Hand Tools, 2-24; etc.). I would like to map these on a spreadsheet that would place the overall topic in the cell at that location (Media or Tools). I'm not sure of the easiest way to complete this. Also, I'm not familiar with VBA. I've used Index and Match to retrieve data from a table but never to put information into a cell. Help please.
 
Hi Todd ,


Two doubts :


1. Would you have two or more products at the same coordinates , or would 1 coordinate have only 1 product ?


2. After having stored your data in such a gridded space , what kind of further operations would you be doing ? Any data manipulation / processing , or just plain data retrieval ?


Narayan
 
Narayan,

One product, one space.

The only additional operations would be moving this product in and out of the space. I would print the grid off for easier location. No additional data analysis is planned at this time.

Thanks,

tdd903
 
Hi Todd ,


Thanks for the clarifications. Actually I should have put in more of my doubts , sorry about that , but here goes :


The system of data mapping that you are looking for , is not really supposed to be a grid based one , since it may have several hierarchical levels ; for instance books will be classified under Fiction & Non-fiction at the highest level ; below Non-fiction you can have categorization based on the subject e.g. Cookery , Sports , Martial Arts ; under Cookery , you can probably have categorization based on origin such as Asian , and within Asian Thai .... the levels can go on.


A grid based one is very simple , but you need to decide whether it will be powerful , flexible , and at the same time unambiguous.


How do you plan to retrieve data ? What will be the search key , the coordinates themselves or the keywords or what ?


Narayan
 
Sorry, I probably should have defined its use a little more. The way I visualized it was to create a map to find the stored product. I anticipate some 'cells' in the grid will have no product and will need no identifying label (Tools). At this point in time, I need no other use than a map to find product from my gridded space. Each grid would either be empty or contain a label. I would Conditional Format the cells by choosing a color for each label for easier use. At this time I have no need for categorizing the product more than this. Does this help?
 
Hi Todd ,


OK. We can take it from here. How are you going to enter data into your grid ?


Will you have a long list of items with the product name , product category and you want Excel to translate this list into a grid ? Do you have this data with you already ? If so , can you upload your sample workbook ?


Narayan
 
Perhaps uploading a workbook or picture somewhere showing a sample of what you have, and what you want would be helpful?
 
The data would be much like I described: Tools, Hand Tools, 15,12; Media, Books, 10,25. The headers for those four columns would be: Group, Category, Row, Column. I'd like the Group to be inside the cell that cooresponded to the coordinates within the grid map.

My data is not in electronic media at this time. Sorry.
 
Hi Todd ,


It's OK. We'll work with the four columns to start with.


If we assume that the row and column header cell contents are used to locate the Group data , then would the Category be the row and column labels ? For instance , if we assume the minimum coordinate is 1 and the maximum is 50 , we would have a grid of 50 rows and 50 columns ; each intersection of row and column would contain a product ; what would the row label and column label for this product be ?


If we assume that column A and row 1 are reserved for the row and column labels , then your actual data range would be 50 rows , starting with row 2 , and 50 columns starting with column B. Now Tools would be in the cell M16 , since column M would be the 12th column from B , and 16 would be the 15th row from 2.


Now , where would Hand Tools and Books figure ?


Narayan
 
Here is a shorthand version of what I am looking for. I'm sorry that I haven't been able to describe it adequately. The top left is my table of products, category, row location and column location. The bottom right is what I am wishing to complete in Excel, although on a larger scale.

The products will change, move, or add to it. I would likely change the list monthly or more frequently. I want the changes I make to the list to automatically change the map at the lower right. This way I will have a map to show my people the location of a group of products. The Category column may be extraneous for what I am attemtpting to create. I want the Group title in the cell of the grid map.

I hope this helps. Thank you very much.


Group Category Row Column

Media Books 1 2

Media Music 2 1

Tools Hand 2 2

Tools Electric 5 1

Storage Small 3 4

Storage Large 4 5


1 2 3 4 5

1 Media

2 Media Tools

3 Storage

4 Storage

5 Tools

(Okay, the grid map did not post to the site the way it was listed in the text box.)
 
Hi Todd ,


Suppose your original list of 4 columns is on Sheet2 ; suppose the column labels ( in row 1 ) are Group , Category , Row and Column ; suppose your data starts from row 2 and goes on till row 7 i.e. your data range is A2:D7.


Suppose your grid map is on Sheet 3 ; suppose your row labels are from row 2 till row 6 , and your column labels are from column B till column F.


In cell B2 , enter the following formula , as an array formula ( using CTRL SHIFT ENTER ) :

[pre]
Code: 
=IFERROR(INDEX(Sheet2!$A$2:$A$7,MATCH($A2&B$1,Sheet2!$C$2:$C$7&Sheet2!$D$2:$D$7,0)),"")
[/pre]
Copy the above formula across and down.


Narayan
 
Thank you Narayan. That formula works.

BTW, I love this site. I've used it a few times since it changed from Pointy Haired Dilbert. Keep up the GREAT work Chandoo and team. Awesome!
 
You must log in or register to reply here.
Back
Top