Not Sure What I'm Asking

[karlitob]

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

 

[Sam_D_Ben]

Something like Pivot table will work for you.

 

[karlitob]

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.

 

[Peter_SSs]

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
	A	B	C
1	States	Counties	Text
2	State 1	County 1	Text 100
3	State 2	County 2	Text 101
4	State 3	County 3	Text 102
5	State 2	County 4	Text 103
6	State 2	County 5	Text 104
7	State 1	County 6	Text 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
	A	B	C
1	State 2	Counties	Text
2		))/(Table1=A$1),ROWS(INDEX(,1):))),"")]County 2	))/(Table1=A$1),ROWS(INDEX(,1):))),"")]Text 101
3		County 4	Text 103
4		County 5	Text 104
5
6
7
8
State 2

Repeat for other states of interest.

 

[karlitob]

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

	A	B	C
State 2	County 2	Text 101
State 2	County 4	Text 103
State 1	County 6	Text 105

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:74px;"><col style="width:90px;"><col style="width:69px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: #99ccff"]States[/TD]
[TD="bgcolor: #99ccff"]Counties[/TD]
[TD="bgcolor: #99ccff"]Text[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]State 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]County 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]Text 100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]State 3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]County 3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]Text 102[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]State 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]County 5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]Text 104[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</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

	A	B	C
State 2
	County 4	Text 103

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:61px;"><col style="width:86px;"><col style="width:69px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: #99ccff"]Counties[/TD]
[TD="bgcolor: #99ccff"]Text[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]County 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]Text 101[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]County 5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]Text 104[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "] [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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.

 

[Peter_SSs]

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.

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.