Value Bands

Status
Not open for further replies.
L

Legacy 479770

Guest
Hello I was hoping that some can help me. I have a tabular data for frameworks that shows a large number of suppliers split by value band and region. The values (approx 48) are text strings for instance: £50m+, £50k-£15m, £250K-£5m; 0 - £3m
1. I am trying first to assign them to the following bands, £1m – £2m; £2m – £3m; £3m – £4m; £4m - £5m; £5m - £10m; £10m – £15m; £15m – £20m; £20m – £30m; £30+
2. Then I want to be able to create a searchable dynamic list and pull out all information by searching for a specific band for instance £1m-£2m it should bring up all framworks and suppliers within that range, so from bands: £50k-£15m, £250K-£5m, but in the same time if £4m - £5m is selected it will also include. £50k-£15m, £250K-£5m

Could someone help please?
Thank you. Kasia
 

Attachments

  • values.PNG
    values.PNG
    202.2 KB · Views: 12

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Would you provide an example of what you want and the solution(s)? Please use XL2BB so that no one helping you has to retype the information.
 
Upvote 0
Would you provide an example of what you want and the solution(s)? Please use XL2BB so that no one helping you has to retype the information.
I am sorry I cant install or drop in without the installation as per steps on the blog, I am blocoked by my administrator.
Re solution: I would like create a dashboard where I can have 3 dynamic validation lists or something similar so can search either on Oragnisation, or the Region, or the Value so I can get all relevant frameworks for my value band, or the supplier I like to chose. I tried to use Filter and Unique functions for Organisations, but dont know how to create one for the Value.
Thank you. Kasia
 
Upvote 0
I'm sorry to be a bit thick, but I still don't know what you're trying to do. Could you write and illustrate a few examples?
 
Upvote 0
Apologies if I am not clear, this is a first time I am posting.
I am trying to search through a worksheet of 5000 records to pull information by searching either by organisation/supplier/framework/band value
I tried to use the Filter and Unique functions to create several dynamic lists for instance to get the list for the organisations I have used:
=IFERROR(INDEX(TableSource[Organisation],MATCH(0,INDEX(COUNTIF($A$1:A1,TableSource[Organisation]),),0)),"").
I have done this for regions and suppliers as well.
Then I have created a simple data validation so I could search by the Organisation, by the Supplier, or by the Region I have used:
=OFFSET ('Dynamic Validation Lists’! $D$3,,,COUNTIF('Dynamic Validation Lists'!$D$3:$D$5000,"?*"))
This allowed me to search through the list by either of the search criteria and for instance return the list of relevant organisations:
=IF(C10='Dynamic Validation Lists'!C1, UNIQUE('Dynamic Validation Lists'!A:A),UNIQUE(FILTER(TableSource[All Organisations],'Search Frameworks'!C10=TableSource[All Approved Suppliers])))
But this only works for single field where I need to pull all of the columns.
I also need to be able to create a list for Values, but need first to assign the Supplier Values ( 48 of them/text ) to my pre defended Value Bands.
As a final output I want to be able to search for ‘and’ or ‘or’ combination of all the search boxes ( organisation/region/supplier/value) so for instance I would like to search for Organisation, then narrow down to select a Region of interest and then narrow down to my Value Band of interest.
Hope this clarifies.
Thank you. Kasia
 
Upvote 0
Thread closed at OP's request
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top