Display all companies of a state, when the state is selected

J

jawahar_otpl

New Member
Joined
Mar 28, 2018
Messages
2
Hi,

I need to see the list of companies and associated details when I select a state. Please check the table I have and the desired list.

List I have:
s1s2s3s4s5s6s7s8s9s10name contact personcontact numberwebsite
ARFLILMSNENVOH Company1 name1number1website1
MANVPA Company2name2number2website2
ARFLGAMSSCTNTX Company3name3number3website3
GA Company4name4number4website4
CAFLGAMANCNYTX Company5name5number5website5
FL Company6name6number6website6
ARGAMAMINCORWV Company7name7number7website7
MNNDWI Company8name8number8website8
CALAMINCPATXWI Company9name9number9website9
CAILMANJNYOHPA Company10name10number10website10
DCFLGAARMDNCSCTNVAWVCompany11name11number11website11
CA Company12 name12number12website12
CADCMINJORTNTX Company13name13number13website13
CALANYOHTXVA Company14name14number14website14
GA Company15name15number15website15
ILKSMO Company16name16number16website16
FLLAMIMSNJNYWA Company17name17number17website17
AKARGAMNORPATN Company18name18number18website18

<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


I need the output to be:
StateAR
namecontact personcontact numberwebsite
Company1name1number1website1
Company3name3number3website3
Company7name7number7website7
Company11name11number11website11
Company18name18number18website18

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Depending upon the state I select from the drop down next to "State", below I need the list of all the companies. Appreciate your help.

Thanks,
Jai
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
I'd recommend using a column E for the row, to avoid doing the calculation intensive array formula more than once. You can hide the column if you want after you put the formula in.

ABCDE
1StateAR
2namecontact personcontact numberwebsiteRow
3Company1name1number1website12
4Company3name3number3website34
5Company7name7number7website78
6Company11name11number11website1112
7Company18name18number18website1819
8

<tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
A3=IF($E3="","",INDEX(Sheet1!K:K,$E3))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
E3{=IFERROR(SMALL(IF(MMULT(IF(Sheet1!$A$2:J$19=$B$1,1,0),{1;1;1;1;1;1;1;1;1;1}),ROW(Sheet1!$A$2:$A$19)),ROWS($E$3:$E3)),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



I put your table on Sheet1, columns A:N. The result sheet looks like that. Put the array formula in E3, change the ranges to match your sheet, confirm with Control+Shift+Enter. Copy down as needed. Then put the INDEX formula in A3, copy down and to the right as needed.

Let us know if this works for you.
 
Last edited:
Upvote 0
Thanks Eric. But I think, there is a miscommunication. Let me explain again. I have list as of data exactly like given below.

SHEET#1

Statescompany namecontact namecontact email
AR|FL|IL|MS|NE|NV|OHcompany#1name#1email#1
MA|NV|PAcompany#2name#2email#2
AR|FL|GA|MS|SC|TN|TXcompany#3name#3email#3
GAcompany#4name#4email#4
CA|FL|GA|MA|NC|NY|TXcompany#5name#5email#5
FLcompany#6name#6email#6
AR|GA|MA|MI|NC|OR|WVcompany#7name#7email#7
MN|ND|WIcompany#8name#8email#8
CA|LA|MI|NC|PA|TX|WIcompany#9name#9email#9
CA|IL|MA|NJ|NY|OH|PAcompany#10name#10email#10
DC|FL|GA|KY|MD|NC|SC|TN|VA|WVcompany#11name#11email#11
CAcompany#12name#12email#12
CA|DC|MI|NJ|OR|TN|TXcompany#13name#13email#13
CA|LA|NY|OH|TX|VAcompany#14name#14email#14
IL|KS|MOcompany#15name#15email#15
FL|LA|MI|MS|NJ|NY|WAcompany#16name#16email#16
AK|AR|GA|MN|OR|PA|TNcompany#17name#17email#17

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

In the next sheet (SHEET#2) I have a drop down list for all states in the US in cell A1. Whenever I select a state from the drop down in A1 in sheet 2, the formula should look for the state in the existing table in sheet 1 and display the contact details of all the companies in sheet 2.

For example if I select the state AR, I must see this below given result in sheet 2. All these companies are functioning in the state AR. If select another state, this list should show the companies operating in that selected state. Hope it is clear now.

AR
namecontact personcontact numberwebsite
Company1name1number1website1
Company3name3number3website3
Company7name7number7website7
Company11name11number11website11
Company18name18number18website18

<tbody>
</tbody>

Regards,
Jai
 
Upvote 0
I thought you had the states in different columns. If they are in the same column, delimited by | like this:

ABCD
1Statescompany namecontact namecontact email
2AR|FL|IL|MS|NE|NV|OHcompany#1name#1email#1
3MA|NV|PAcompany#2name#2email#2
4AR|FL|GA|MS|SC|TN|TXcompany#3name#3email#3
5GAcompany#4name#4email#4
6CA|FL|GA|MA|NC|NY|TXcompany#5name#5email#5
7FLcompany#6name#6email#6
8AR|GA|MA|MI|NC|OR|WVcompany#7name#7email#7
9MN|ND|WIcompany#8name#8email#8
10CA|LA|MI|NC|PA|TX|WIcompany#9name#9email#9
11CA|IL|MA|NJ|NY|OH|PAcompany#10name#10email#10
12DC|FL|GA|KY|MD|NC|SC|TN|VA|WVcompany#11name#11email#11
13CAcompany#12name#12email#12
14CA|DC|MI|NJ|OR|TN|TXcompany#13name#13email#13
15CA|LA|NY|OH|TX|VAcompany#14name#14email#14
16IL|KS|MOcompany#15name#15email#15
17FL|LA|MI|MS|NJ|NY|WAcompany#16name#16email#16
18AK|AR|GA|MN|OR|PA|TNcompany#17name#17email#17

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



Then you can use this version:

ABCDE
1StateAR
2Company NameContact NameContact EmailRow
3company#1name#1email#12
4company#3name#3email#34
5company#7name#7email#78
6company#17name#17email#1718
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
A3=IF($E3="","",INDEX(Sheet1!B:B,$E3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
E3{=IFERROR(SMALL(IF(ISNUMBER(SEARCH("|"&$B$1&"|","|"&Sheet1!$A$2:$A$18&"|")),ROW(Sheet1!$A$2:$A$18)),ROWS($E$3:$E3)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Populating a ListBox with Columns Using Pre-Filled Array
Replies
5
Views
389
Legacy 143009
L
C
  • Question
Match Top Results
Replies
3
Views
304
Fluff
Fluff
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
942
pawcoyote
P
J
  • Solved
Copy row if
Replies
3
Views
341
Fluff
Fluff
S
  • Question
UDF not working as expected
Replies
2
Views
576
spydey
S

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

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