Not Sure What I'm Asking

karlitob

New Member
Joined
Jun 24, 2017
Messages
38
Hi all,

So I have a raw data table on one tab in a worksheet. Its got about 30,000 rows
- Column 1: 50 Unique values (US States)
- Column 2: X Unique values (Counties with those States)
- Column 3: Short amount of text entered into a cell (I know, but its done now).

I want to create 5 reports based on 5 US States which uses the 'relevant' counties (column 2) and relevant text (column 3). In other words, if I select New York, then I want relevant column 2 and column 3 to populate in a table.

So I've set up 5 new tabs in this worksheet. Now I know I can just filter the raw data file by state, then copy and paste what I need into each of the 5 tabs. But, there will be more raw data added to the original raw data file and I would like to automate this process.

All advice very much welcomed. Thank you
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Thanks. But I want to return the text that’s in each of the cells on column 3. That’s the key part of the report.
 
Upvote 0
So I have a raw data table on one tab in a worksheet.
Is this a formal Excel Table (ListObject - usually with automatically formatted banded rows) or just three standard columns of data.
Suggestion below assumes a formal table (Table1) but suggestion could be modified if you just have standard columns of data.

My sample dummy data looks like this.

Excel Workbook
ABC
1StatesCountiesText
2State 1County 1Text 100
3State 2County 2Text 101
4State 3County 3Text 102
5State 2County 4Text 103
6State 2County 5Text 104
7State 1County 6Text 105
Sheet1



In the other sheet
Put the state of interest in A1 (or it could be anywhere)
Add headings in B1:C1 as shown
Select B1:C1 and on the Insert ribbon tab choose Table -> My table has headers -> OK
Fill in the formulas as shown and copy them down as far as you might ever need.

Excel Workbook
ABC
1State 2CountiesText
2))/(Table1=A$1),ROWS(INDEX(,1):))),"")]County 2))/(Table1=A$1),ROWS(INDEX(,1):))),"")]Text 101
3County 4Text 103
4County 5Text 104
5
6
7
8
State 2


Repeat for other states of interest.
 
Upvote 0
Is this a formal Excel Table (ListObject - usually with automatically formatted banded rows) or just three standard columns of data.
Suggestion below assumes a formal table (Table1) but suggestion could be modified if you just have standard columns of data.

My sample dummy data looks like this.

Sheet1

ABC
1StatesCountiesText
2State 1County 1Text 100
3State 2County 2Text 101
4State 3County 3Text 102
5State 2County 4Text 103
6State 2County 5Text 104
7State 1County 6Text 105

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:74px;"><col style="width:90px;"><col style="width:69px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


In the other sheet
Put the state of interest in A1 (or it could be anywhere)
Add headings in B1:C1 as shown
Select B1:C1 and on the Insert ribbon tab choose Table -> My table has headers -> OK
Fill in the formulas as shown and copy them down as far as you might ever need.

State 2

ABC
1State 2CountiesText
2 County 2Text 101
3 County 4Text 103
4 County 5Text 104
5
6
7
8

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:61px;"><col style="width:86px;"><col style="width:69px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B2=IFERROR(INDEX(Table1[Counties],AGGREGATE(15,6,(ROW(Table1[States])-ROW(Table1[[#Headers],[States]]))/(Table1[States]=A$1),ROWS(INDEX([Counties],1):[@Counties]))),"")
C2=IFERROR(INDEX(Table1[Text],AGGREGATE(15,6,(ROW(Table1[States])-ROW(Table1[[#Headers],[States]]))/(Table1[States]=A$1),ROWS(INDEX([Counties],1):[@Counties]))),"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Repeat for other states of interest.


Absolutely fantastic. Thank you SOOOO much. I know this is probably easy to you - and I'm sure took a long time to learn - but this really is brilliant - thank you for sharing and helping.
 
Upvote 0
Absolutely fantastic. Thank you SOOOO much. I know this is probably easy to you - and I'm sure took a long time to learn - but this really is brilliant - thank you for sharing and helping.
You are very welcome & thanks for the follow-up.

Yes, it does take a long time to learn - but it's fun. :biggrin::cool:

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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