jonsharman
New Member
- Joined
- Jan 4, 2014
- Messages
- 28
Hi,
I’m hoping that I might be able to get some advice with a complex search and return query in Excel that has stumped my thought process over the past couple of days. I’ll give some background to the problem I trying to resolve so as to add some context to the required solution. An out-dated car manufacturing system produces a report of stock at a number of dealers including things such as model, colour, upholstery, options and price etc. However the reports are two separate downloads and only display limited information about the specification – for example the options fitted to the car are one long string of text and just show a three digit code of which there are over 1,000 different choices. Neither report are really helpful for those people who would need to search for a car – first of all they would need to know the codes of the options or packs that the customer would be looking for and, secondly, the options are not always in the same order so a simple auto-filter doesn’t work.
I have written some code to split out the codes for the packs and options and used Macro to copy across the individual vehicle order numbers then INDEX MATCH to tie the data on the two separate reports together. This sheet also looks up the name of the Packs / Options and consolidates this into one more-useable list with the actual description of the Packs / Options from one master list.
Now the tricky part is creating a page that has the ability to search for certain Packs and / or Options within each of the model ranges and return order numbers that contain the Packs / Options that the user has selected. There are some considerations:
· The packages and options are not always located in the same column and may appear in one of four columns for Packs and one of twenty-five columns for Options.
· Each vehicle may have further options or packs that the user is not necessarily searching for but would still want to see as this may be a suitable alternative for the customer.
· Some order types need to be excluded from the search results as these are already sold orders or have been allocated for specific uses and are therefore not available for sale.
I have attached the workbook to the post so you can see how the data is presented.
I’m not sure how best to approach this – is it a complex array formula with multiple criteria to return the order numbers though this may slow the workbook down? Is it best to use a Pivot Table or a Slicer to chop up the data I want to find? Or is a Macro a more efficient tool to avoid the workbook being slow?Hi,
I’m hoping that I might be able to get some advice with a complex search and return query in Excel that has stumped my thought process over the past couple of days. I’ll give some background to the problem I trying to resolve so as to add some context to the required solution. An out-dated car manufacturing system produces a report of stock at a number of dealers including things such as model, colour, upholstery, options and price etc. However the reports are two separate downloads and only display limited information about the specification – for example the options fitted to the car are one long string of text and just show a three digit code of which there are over 1,000 different choices. Neither report are really helpful for those people who would need to search for a car – first of all they would need to know the codes of the options or packs that the customer would be looking for and, secondly, the options are not always in the same order so a simple auto-filter doesn’t work.
I have written some code to split out the codes for the packs and options and used Macro to copy across the individual vehicle order numbers then INDEX MATCH to tie the data on the two separate reports together. This sheet also looks up the name of the Packs / Options and consolidates this into one more-useable list with the actual description of the Packs / Options from one master list.
Now the tricky part is creating a page that has the ability to search for certain Packs and / or Options within each of the model ranges and return order numbers that contain the Packs / Options that the user has selected. There are some considerations:
· The packages and options are not always located in the same column and may appear in one of four columns for Packs and one of twenty-five columns for Options.
· Each vehicle may have further options or packs that the user is not necessarily searching for but would still want to see as this may be a suitable alternative for the customer.
· Some order types need to be excluded from the search results as these are already sold orders or have been allocated for specific uses and are therefore not available for sale.
I’m not sure how best to approach this – is it a complex array formula with multiple criteria to return the order numbers though this may slow the workbook down? Is it best to use a Pivot Table or a Slicer to chop up the data I want to find? Or is a Macro a more efficient tool to avoid the workbook being slow?
[TABLE="width: 8701"]
<colgroup><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col span="2"><col span="2"><col><col span="6"><col><col span="2"><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Order Number[/TD]
[TD]Model Type[/TD]
[TD]Description[/TD]
[TD]Order Type[/TD]
[TD]Financial Status[/TD]
[TD]Colour[/TD]
[TD]Upholstery[/TD]
[TD]Transmission[/TD]
[TD]List Price[/TD]
[TD]CO2 Value[/TD]
[TD]RFL Cost[/TD]
[TD]OTR[/TD]
[TD]Package1[/TD]
[TD]Package2[/TD]
[TD]Package3[/TD]
[TD]Package4[/TD]
[TD]Option1[/TD]
[TD]Option2[/TD]
[TD]Option3[/TD]
[TD]Option4[/TD]
[TD]Option5[/TD]
[TD]Option6[/TD]
[TD]Option7[/TD]
[TD]Option8[/TD]
[TD]Option9[/TD]
[TD]Option10[/TD]
[TD]Option11[/TD]
[TD]Option12[/TD]
[TD]Option13[/TD]
[TD]Option14[/TD]
[TD]Option15[/TD]
[TD]Option16[/TD]
[TD]Option17[/TD]
[TD]Option18[/TD]
[TD]Option19[/TD]
[TD]Option20[/TD]
[TD]Option21[/TD]
[TD]Option22[/TD]
[TD]Option23[/TD]
[TD]Option24[/TD]
[TD]Option25[/TD]
[/TR]
[TR]
[TD="align: right"]3604955[/TD]
[TD]MINI (R61)[/TD]
[TD]R61 JCW[/TD]
[TD]OS[/TD]
[TD]P[/TD]
[TD]Absolute Black[/TD]
[TD]Leather Gravity-Carbon Black[/TD]
[TD]MANUAL[/TD]
[TD] £ 28,416.67[/TD]
[TD="align: right"]169[/TD]
[TD] £ 300.00[/TD]
[TD] £ 34,975.00[/TD]
[TD]Media Pack XL[/TD]
[TD]Chili Pack[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Run-flat tyres[/TD]
[TD]19" Cross Spoke Crusher Black Light Alloy Wheels[/TD]
[TD]Roof and mirror caps in Red[/TD]
[TD]Sports stripes in Red[/TD]
[TD]Seat heating for driver and front passenger[/TD]
[TD]Inlay Colour Line - Carbon Black[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3685809[/TD]
[TD]MINI (R61)[/TD]
[TD]R61 SD ALL4[/TD]
[TD]OS[/TD]
[TD]P[/TD]
[TD]Midnight Grey[/TD]
[TD]Cloth/leather Hot Cross Carbon Black[/TD]
[TD]AUTOMATIC[/TD]
[TD] £ 25,633.33[/TD]
[TD="align: right"]161[/TD]
[TD] £ 185.00[/TD]
[TD] £ 31,520.00[/TD]
[TD]John Cooper Works Chili Pack[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Automatic transmission[/TD]
[TD]Run-flat tyres[/TD]
[TD]Folding exterior mirrors[/TD]
[TD]Roof and mirror caps in black[/TD]
[TD]Inlay Colour Line - Carbon Black[/TD]
[TD]Harman Kardon® Hi-Fi system[/TD]
[TD]Visual Boost Radio[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3686844[/TD]
[TD]MINI (F54) Clubman[/TD]
[TD]F54 Cooper S[/TD]
[TD]OS[/TD]
[TD]P[/TD]
[TD]Chili Red[/TD]
[TD]Cloth/leather combination Cord Carbon Bl[/TD]
[TD]AUTOMATIC[/TD]
[TD] £ 27,216.67[/TD]
[TD="align: right"]137[/TD]
[TD] £ 130.00[/TD]
[TD] £ 33,365.00[/TD]
[TD]John Cooper Works Chili Pack[/TD]
[TD]Media Pack XL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Run-flat tyres[/TD]
[TD]Sport automatic transmission[/TD]
[TD]Bonnet stripes in black[/TD]
[TD]Heated front windscreen[/TD]
[TD]Roof and mirror caps in black[/TD]
[TD]Roof rails[/TD]
[TD]Panoramic electric glass sunroof[/TD]
[TD]Darkened rear glass[/TD]
[TD]Seat adjustment, electric, with memory[/TD]
[TD]Through-loading system[/TD]
[TD]Seat heating for driver and front passenger[/TD]
[TD]Interior Surfaces - Piano Black[/TD]
[TD]Rain sensor and automatic headlight activation[/TD]
[TD]Cruise control with brake function[/TD]
[TD]MINI Head-up Display[/TD]
[TD]MINI Connected[/TD]
[TD]MINI Connected XL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
I’m hoping that I might be able to get some advice with a complex search and return query in Excel that has stumped my thought process over the past couple of days. I’ll give some background to the problem I trying to resolve so as to add some context to the required solution. An out-dated car manufacturing system produces a report of stock at a number of dealers including things such as model, colour, upholstery, options and price etc. However the reports are two separate downloads and only display limited information about the specification – for example the options fitted to the car are one long string of text and just show a three digit code of which there are over 1,000 different choices. Neither report are really helpful for those people who would need to search for a car – first of all they would need to know the codes of the options or packs that the customer would be looking for and, secondly, the options are not always in the same order so a simple auto-filter doesn’t work.
I have written some code to split out the codes for the packs and options and used Macro to copy across the individual vehicle order numbers then INDEX MATCH to tie the data on the two separate reports together. This sheet also looks up the name of the Packs / Options and consolidates this into one more-useable list with the actual description of the Packs / Options from one master list.
Now the tricky part is creating a page that has the ability to search for certain Packs and / or Options within each of the model ranges and return order numbers that contain the Packs / Options that the user has selected. There are some considerations:
· The packages and options are not always located in the same column and may appear in one of four columns for Packs and one of twenty-five columns for Options.
· Each vehicle may have further options or packs that the user is not necessarily searching for but would still want to see as this may be a suitable alternative for the customer.
· Some order types need to be excluded from the search results as these are already sold orders or have been allocated for specific uses and are therefore not available for sale.
I have attached the workbook to the post so you can see how the data is presented.
I’m not sure how best to approach this – is it a complex array formula with multiple criteria to return the order numbers though this may slow the workbook down? Is it best to use a Pivot Table or a Slicer to chop up the data I want to find? Or is a Macro a more efficient tool to avoid the workbook being slow?Hi,
I’m hoping that I might be able to get some advice with a complex search and return query in Excel that has stumped my thought process over the past couple of days. I’ll give some background to the problem I trying to resolve so as to add some context to the required solution. An out-dated car manufacturing system produces a report of stock at a number of dealers including things such as model, colour, upholstery, options and price etc. However the reports are two separate downloads and only display limited information about the specification – for example the options fitted to the car are one long string of text and just show a three digit code of which there are over 1,000 different choices. Neither report are really helpful for those people who would need to search for a car – first of all they would need to know the codes of the options or packs that the customer would be looking for and, secondly, the options are not always in the same order so a simple auto-filter doesn’t work.
I have written some code to split out the codes for the packs and options and used Macro to copy across the individual vehicle order numbers then INDEX MATCH to tie the data on the two separate reports together. This sheet also looks up the name of the Packs / Options and consolidates this into one more-useable list with the actual description of the Packs / Options from one master list.
Now the tricky part is creating a page that has the ability to search for certain Packs and / or Options within each of the model ranges and return order numbers that contain the Packs / Options that the user has selected. There are some considerations:
· The packages and options are not always located in the same column and may appear in one of four columns for Packs and one of twenty-five columns for Options.
· Each vehicle may have further options or packs that the user is not necessarily searching for but would still want to see as this may be a suitable alternative for the customer.
· Some order types need to be excluded from the search results as these are already sold orders or have been allocated for specific uses and are therefore not available for sale.
I’m not sure how best to approach this – is it a complex array formula with multiple criteria to return the order numbers though this may slow the workbook down? Is it best to use a Pivot Table or a Slicer to chop up the data I want to find? Or is a Macro a more efficient tool to avoid the workbook being slow?
[TABLE="width: 8701"]
<colgroup><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col span="2"><col span="2"><col><col span="6"><col><col span="2"><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Order Number[/TD]
[TD]Model Type[/TD]
[TD]Description[/TD]
[TD]Order Type[/TD]
[TD]Financial Status[/TD]
[TD]Colour[/TD]
[TD]Upholstery[/TD]
[TD]Transmission[/TD]
[TD]List Price[/TD]
[TD]CO2 Value[/TD]
[TD]RFL Cost[/TD]
[TD]OTR[/TD]
[TD]Package1[/TD]
[TD]Package2[/TD]
[TD]Package3[/TD]
[TD]Package4[/TD]
[TD]Option1[/TD]
[TD]Option2[/TD]
[TD]Option3[/TD]
[TD]Option4[/TD]
[TD]Option5[/TD]
[TD]Option6[/TD]
[TD]Option7[/TD]
[TD]Option8[/TD]
[TD]Option9[/TD]
[TD]Option10[/TD]
[TD]Option11[/TD]
[TD]Option12[/TD]
[TD]Option13[/TD]
[TD]Option14[/TD]
[TD]Option15[/TD]
[TD]Option16[/TD]
[TD]Option17[/TD]
[TD]Option18[/TD]
[TD]Option19[/TD]
[TD]Option20[/TD]
[TD]Option21[/TD]
[TD]Option22[/TD]
[TD]Option23[/TD]
[TD]Option24[/TD]
[TD]Option25[/TD]
[/TR]
[TR]
[TD="align: right"]3604955[/TD]
[TD]MINI (R61)[/TD]
[TD]R61 JCW[/TD]
[TD]OS[/TD]
[TD]P[/TD]
[TD]Absolute Black[/TD]
[TD]Leather Gravity-Carbon Black[/TD]
[TD]MANUAL[/TD]
[TD] £ 28,416.67[/TD]
[TD="align: right"]169[/TD]
[TD] £ 300.00[/TD]
[TD] £ 34,975.00[/TD]
[TD]Media Pack XL[/TD]
[TD]Chili Pack[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Run-flat tyres[/TD]
[TD]19" Cross Spoke Crusher Black Light Alloy Wheels[/TD]
[TD]Roof and mirror caps in Red[/TD]
[TD]Sports stripes in Red[/TD]
[TD]Seat heating for driver and front passenger[/TD]
[TD]Inlay Colour Line - Carbon Black[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3685809[/TD]
[TD]MINI (R61)[/TD]
[TD]R61 SD ALL4[/TD]
[TD]OS[/TD]
[TD]P[/TD]
[TD]Midnight Grey[/TD]
[TD]Cloth/leather Hot Cross Carbon Black[/TD]
[TD]AUTOMATIC[/TD]
[TD] £ 25,633.33[/TD]
[TD="align: right"]161[/TD]
[TD] £ 185.00[/TD]
[TD] £ 31,520.00[/TD]
[TD]John Cooper Works Chili Pack[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Automatic transmission[/TD]
[TD]Run-flat tyres[/TD]
[TD]Folding exterior mirrors[/TD]
[TD]Roof and mirror caps in black[/TD]
[TD]Inlay Colour Line - Carbon Black[/TD]
[TD]Harman Kardon® Hi-Fi system[/TD]
[TD]Visual Boost Radio[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3686844[/TD]
[TD]MINI (F54) Clubman[/TD]
[TD]F54 Cooper S[/TD]
[TD]OS[/TD]
[TD]P[/TD]
[TD]Chili Red[/TD]
[TD]Cloth/leather combination Cord Carbon Bl[/TD]
[TD]AUTOMATIC[/TD]
[TD] £ 27,216.67[/TD]
[TD="align: right"]137[/TD]
[TD] £ 130.00[/TD]
[TD] £ 33,365.00[/TD]
[TD]John Cooper Works Chili Pack[/TD]
[TD]Media Pack XL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Run-flat tyres[/TD]
[TD]Sport automatic transmission[/TD]
[TD]Bonnet stripes in black[/TD]
[TD]Heated front windscreen[/TD]
[TD]Roof and mirror caps in black[/TD]
[TD]Roof rails[/TD]
[TD]Panoramic electric glass sunroof[/TD]
[TD]Darkened rear glass[/TD]
[TD]Seat adjustment, electric, with memory[/TD]
[TD]Through-loading system[/TD]
[TD]Seat heating for driver and front passenger[/TD]
[TD]Interior Surfaces - Piano Black[/TD]
[TD]Rain sensor and automatic headlight activation[/TD]
[TD]Cruise control with brake function[/TD]
[TD]MINI Head-up Display[/TD]
[TD]MINI Connected[/TD]
[TD]MINI Connected XL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
