Need help aggregating data

**glazedfaith** · 11-29-2010, 02:14 PM

I have no idea where to go from here.

I'm trying to make a dynamic list of items and their locations.

I have 7 bays labeled A-G which are each divided into 2 to 4 different sections labeled A1, A2, B1, B2, etc. In each section I have between 1 and 18 items which are each numbered. I have them in sheets by bay now, but I need an additional listing that has all the item numbers in numerical order, and lists which section(s) each can be found in.

Where do I go from here?

**Lifesigns** · 11-29-2010, 02:29 PM

Could you perhaps provide an example of what you have so far? Sometimes I create what I call unique ID's for each item in a list so I can find them with some kind of lookup.

Perhaps we can achieve the same here.

**glazedfaith** · 11-29-2010, 02:39 PM

What I have is childishly basic, but for now it is functional. I haven't done any serious excel work in about 10 years. I would probably have to completely recreate the whole thing to get any workability out of it.

Basically I'd like the first page to be a master listing showing where each item number can be found. Please forgive my lack of knowledge on the matter. Thanks for your help.

**Lifesigns** · 11-29-2010, 03:44 PM

I've had a bit of a look and was just wondering, do you have a list of all the items i.e. do you know all the SKU?

What version of Excel are you using?

**glazedfaith** · 11-29-2010, 04:03 PM

I can easily assemble them if I need to. I'd just copy and paste them all into a list, sort, and remove duplicates. I'm using excel 2007

**Lifesigns** · 11-30-2010, 01:25 PM

Hi, I've attached an example of a way I think you can achieve this.

All you need to do is add all the items to the relevant Bays and then list them all on the Index sheet, their location will appear in Column C. You can sort the SKU's or filter for specific ones and still get their locations you can also sort based on locations.

I've created Hyperlinks to Bays A & B on the left so you can easily navigate to where the items are. When you click it takes you to the Bay number and then you just have to expand the group. You can create the rest if you think that's useful.

There are always many ways of doing these kinds of things but I find it is easier to work with one data sheet, especially if you have to make changes.

I understand SKU's are Unique but just so you know the formula will only find the first Location of a SKU if it is listed in more than one bay.

The formulas are on the Bays sheet also grouped in Columns E-H. I've also changed the file format to *.xlsx it's smaller and has all the benefits of Excel 2007.

Hope that helps

**royUK** · 11-30-2010, 01:29 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.