A lot of variables in data...

[mmoor29]

So, here's my dilemma:

I do Loss Prevention work, and I'm trying to pull information from a worksheet into a simplified table for a region of about 150 stores, which would be 150 different excel spreadsheets.

The problem is that the information varies from each store...

Example:
Store 1234 has 50 types of televisions at different prices. Each item is grouped into categories (i.e. TELEVISIONS) I can subtotal to get a total of TELEVISION loss for that store.

Store 5678 has 40 types of televisions at different prices. Those are also grouped into the same category. I can also subtotal to get the total TELEVISION loss.

When I use macros to pull the information, it's pulling the CELLS, not the values I need it to pull. Since there is no constant between the number of televisions between the stores, it's pulling CELL information, which works great for store 1234, but store 5678 information is all wrong. I hope this made sense to someone out there and can possibly help me with code, or is this just a pipe dream? Thanks in advance!

Possible promotion riding on this (ha!),
Mike

 

[justmeok]

I had a similar problem and I solved it by creating a pivot table. Are you familiar with them? If not they only take minutes to create once you have done the first one and are so flexible. If you have one of the newer versions of excel its even easier than before as you won't have to have your data in a single worksheet. Alternatively, can you export the data into a single worksheet from the source?

BTW my boss loves it and now wants pivot reports for everything so be warned!

 

[mmoor29]

You are a genius, justmeok! I'll use the pivot table as a constant to copy and paste my data using the macros, since you can tell Excel where to place the pivot table. That way, my data will always be in the same cells every time. Thank you!

I actually like pivot tables a lot and they are really useful (which also boggles my mind as to why I didn't think of this before lol)...the only issue is, not everyone is an Excel enthusiast like me or really even knows how to use any of the features of Excel without wanting to put it down and say "forget it"! You are a life saver!

 

[justmeok]

Wow thank you high praise indeed although I don't feel it is entirely warranted but I am so glad to have been able to help!

I am totally loving pivots and especially the slicer and timeline functions, they are awesome! Like you I love to look for ways that excel can solve the issues for me instead of having to do lots of repetitive manual work myself....hmmm maybe that just makes me lazy