Extract specific data from a sheet created by a non-expert Excel user

[gak67]

I am wanting to extract the sales data by month for just the 2014/2015 financial year (Apr-14 to Mar-15) from the attached sheet. Sounds easy enough, but if you have a look at the sheet it's not. I can get the sales figures easily enough but I need the SKU number with it and that is where I am getting stuck.

I need to do this monthly and there are about 40 sheets like this (one for each supplier).

The sheet itself cannot be changed around permanently (the guy who created it has it as read only access to everybody but him) but as it is read only any extra columns to be added etc is not an issue.

The sheet is pretty much as the original with the product names changed and customer and supplier names removed. This spreadsheet has been developed over some time and there is a lot of variation in the sheet to be wary of. I have tried to build in some of that variation with the sample data. There are 2 markets shown (NZ and Aus) and the same description can be used in each market but the SKU's are different (the Aus SKU is the NZ SKU with an 'a' at the end). Product names can vary between different parts of the same sheet, there are varying numbers of years of data per product, hidden grouped rows etc. The products with '???' as a SKU number are fine - they are products we are developing and will sell, but the SKU has not yet been created. I can deal with those as they arise.

Please help me find a way to do this that's not going to take me half a day per month.

Supplier1.xlsx

[humdingaling]

the data seems nice and square

open to macro?

find "COVER" from cell above take the value from 7 rows above
that way you will have each box with SKU number
from there you should be able to extra data pretty easily?

Sample of what i had in mind?

[gak67]

Yes I am open to using a macro and I like what you have done, however, the other sheets for the other suppliers won't necessarily have the data starting from row 47. Is there a way around that?

[humdingaling]

you can start it where ever you want
it wont have any effect until "COVER" appears

Please Login or Register  to view this content.

[gak67]

Thanks. That will get me 95% of the way there. There are some products for some suppliers where there are more than 7 rows of data per year (I warned there was a lot of variation in the sheet, but forgot about that one when I uploaded the sample sheet).

[humdingaling]

ok
added extra logic to it

Please Login or Register  to view this content.

assumptions in code
no more than 12 lines per box....increase if you need
no less than 3 lines per box..decrease if needed

what this code does now is with in the loop it does a count back when it finds "COVER" to find the cell below it which should be product description
looks back row by row to find the description
takes that row -1 to get the row with the sku number and inserts that into the current J row

[gak67]

Sorry it's taken so long to get back to this. I have tried the macro as suggested and it mostly works great, but I have built in some more of the variations I have to contend with and it doesn't work fully. I realise I'm going to have to do some manual work on this still anyway, and the macro provided has helped, but if anybody can improve on this I'd appreciate it.

See new sheet attached:Supplier1.xlsx

[humdingaling]

first problem
row 92
Product 2
row 99
Product2
this will not trigger macro...
i cant really help with mispelt or inherently different product names
possible solution for this is not to use description but to use "SALES" as your reference point instead?

however the second problem you have posed is a challenge...mainly because i don't know your frequency and i don't your know full set of issues
is it always section 19a ? or is it something weird all the time
obviously a solution around this can only be gander with the true reflection of the data set your facing

[humdingaling]

scratch that i noticed one of your boxes has Share% at the top line

[humdingaling]

Please Login or Register  to view this content.

code to work with "SALES" as trigger and check for % share" (this is case sensitive)
depending on your response on Section 19a will determine how to bypass it

[gak67]

I have approached this from a formula perspective rather than a VBA perspective, and I have had the same frustrations as you. The change in name (shown in the sample as "Product 2" and "Product2") is not uncommon in the full workbook. What has traditionally happened is as a new product has been developed it has had the basic info entered and the following year (or sometimes 2 or 3 years later) further detail is added to the product name, but the previous descriptions are not updated. It's on my to do list to rework this spreadsheet so the guy who uses it can have it do what he wants, but we can extract the data from it more easily, but that will be a number of months away.

I have spent enough time on this now and what I have between what you have given me and what I have done myself has trimmed a significant amount of time off my monthly task. Thank you.