Hi
I am trying to automate populating sales history into a forecasting worksheet that I am working on, except I have to do a separate sheet for 45 warehouses that contains over 500 brand/styles to populate sales history for. Please see the sample data below.
I want to take Sheet 1 data dump from my system and rearrange the Styles under Brand in Sheet 2 with Qty Sold and Sales Revenues in separate sections.
I have already formatted Sheet 2 to show Brand and Style rollup by month.
Will I run into problems if the Style field for other brands are not all identical? i.e. some are alpha-numeric while others are numeric but with different field lenghts (from 1 digit to 5 or 6 digits).
Would it be possible to do a conditional Vlookup from Sheet 2 to lookup a specific brand and return an exact style match?
I would really appreciate any help in getting some formulas to automate the process as my stomach churns thinking about trying to do all this with cut and paste.
Thanks!
Sheet 1 -- Data Dump from my system
YrMonth Brand Style Qty Revenue
200501 Cool 2031 99 3000
200501 Cool 2033 111 5000
200501 Cool 2034 336 10495
200501 Cool 2091 456 2221
200501 Cool 2092 661 8753
200501 Cool 2094 2347 3245
200501 Cool 2095 235 245
200501 Cool 2098 22145 52323
200501 Cool 2211 345 456
200501 Cool 2213 783 22257
Sheet 2 -- Forecast Worksheet
Qty Jan-05 Feb-05 Mar-05
Cool
2031
2033
2034
2091
2092
2094
2095
2098
2211
2213
I am trying to automate populating sales history into a forecasting worksheet that I am working on, except I have to do a separate sheet for 45 warehouses that contains over 500 brand/styles to populate sales history for. Please see the sample data below.
I want to take Sheet 1 data dump from my system and rearrange the Styles under Brand in Sheet 2 with Qty Sold and Sales Revenues in separate sections.
I have already formatted Sheet 2 to show Brand and Style rollup by month.
Will I run into problems if the Style field for other brands are not all identical? i.e. some are alpha-numeric while others are numeric but with different field lenghts (from 1 digit to 5 or 6 digits).
Would it be possible to do a conditional Vlookup from Sheet 2 to lookup a specific brand and return an exact style match?
I would really appreciate any help in getting some formulas to automate the process as my stomach churns thinking about trying to do all this with cut and paste.
Thanks!
Sheet 1 -- Data Dump from my system
YrMonth Brand Style Qty Revenue
200501 Cool 2031 99 3000
200501 Cool 2033 111 5000
200501 Cool 2034 336 10495
200501 Cool 2091 456 2221
200501 Cool 2092 661 8753
200501 Cool 2094 2347 3245
200501 Cool 2095 235 245
200501 Cool 2098 22145 52323
200501 Cool 2211 345 456
200501 Cool 2213 783 22257
Sheet 2 -- Forecast Worksheet
Qty Jan-05 Feb-05 Mar-05
Cool
2031
2033
2034
2091
2092
2094
2095
2098
2211
2213