JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Formula to parse data correctly without pivot table
Hey Team,
I am working on identifying 4 groups of people based on demographic data and want to be able to quickly pull locations that match the income range along with the row information as shown below:
Also is it possible to create a radial button to that can show sheet 2 data by clicking?
A B C D E F G H I J K 1 Location Average Income City State Average Rent Average Mortgate Customer Segment $0-$49,000 Additional row information populated Customer Segment $50,000-$99,999 Additional row information populated 2 1
$108,634
City1 State1 AR1 AM1 =all locations with income range above 3 2
$64,476
City2 State2 AR2 AM2 4 3
$69,827
City3 State3 AR3 AM3 5 4
$71,567
City4 State4 AR4 AM4 6 5
$78,763
City5 State5 AR5 AM5 7 7
$72,613
City6 State6 AR6 AM6 8 8
$64,444
City7 State7 AR7 AM7 9 9
$58,744
City8 State8 AR8 AM8 10 10
$55,995
City9 State9 AR9 AM9 11 11
$59,230
City10 State10 AR10 AM10 Sheet 2 -> More segments -> 12 12
$77,989
City11 State11 AR11 AM11 13 13
$61,778
City12 State12 AR12 AM12 14 14
$60,383
City13 State13 AR13 AM13 15 15
$55,867
City14 State14 AR14 AM14 16 16
$57,992
City15 State15 AR15 AM15 17 17
$60,678
City16 State16 AR16 AM16 18 18
$51,879
City17 State17 AR17 AM17 19 19
$54,253
City18 State18 AR18 AM18 20 20
$66,047
City19 State19 AR19 AM19 21 21
$68,186
City20 State20 AR20 AM20 22 22
$63,863
City21 State21 AR21 AM21 23 23
$62,101
City22 State22 AR22 AM22 24 24
$175,000
City23 State23 AR23 AM23
Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
along with the row information as shown below
You haven't actually shown any expected results, you just have a column called "Additional row information populated" which doesn't tell us much.
Understood and below should bring clarity:
Sheet 1
A B C D E F 1
Location Average Income City State Average Rent Average Mortgate 2
1
$45,231
City1 State1 AR1 AM1 3
2
$108,634
City2 State2 AR2 AM2 4
3
$69,827
City3 State3 AR3 AM3 5
4
$71,567
City4 State4 AR4 AM4 6
5
$78,763
City5 State5 AR5 AM5 7
7
$72,613
City6 State6 AR6 AM6 8
8
$64,444
City7 State7 AR7 AM7 9
9
$58,744
City8 State8 AR8 AM8 10
10
$55,995
City9 State9 AR9 AM9 11
11
$59,230
City10 State10 AR10 AM10 12
12
$77,989
City11 State11 AR11 AM11 13
13
$61,778
City12 State12 AR12 AM12 14
14
$60,383
City13 State13 AR13 AM13 15
15
$55,867
City14 State14 AR14 AM14 16
16
$57,992
City15 State15 AR15 AM15 17
17
$60,678
City16 State16 AR16 AM16 18
18
$51,879
City17 State17 AR17 AM17 19
19
$54,253
City18 State18 AR18 AM18 20
20
$66,047
City19 State19 AR19 AM19 21
21
$68,186
City20 State20 AR20 AM20 22
22
$63,863
City21 State21 AR21 AM21 23
23
$62,101
City22 State22 AR22 AM22 24
24
$175,000
City23 State23 AR23 AM23
Sheet 2
A B C D E F 1 Customer Segment 1 ($0-$49,000) 2 Location Average Income City State Average Rent Average Mortgate 3 1
$45,231
City1 State1 AR1 AM1 4 5 6 7 8 9 10 11 12 13 14
G H I J K L Customer Segment 2 ($50,000-$99,999) Location Average Income City State Average Rent Average Mortgate 3
$69,827
City3 State3 AR3 AM3 4
$71,567
City4 State4 AR4 AM4 5
$78,763
City5 State5 AR5 AM5 7
$72,613
City6 State6 AR6 AM6 8
$64,444
City7 State7 AR7 AM7 9
$58,744
City8 State8 AR8 AM8 10
$55,995
City9 State9 AR9 AM9 11
$59,230
City10 State10 AR10 AM10 12
$77,989
City11 State11 AR11 AM11 13
$61,778
City12 State12 AR12 AM12 14
$60,383
City13 State13 AR13 AM13 15
$55,867
City14 State14 AR14 AM14 16
$57,992
City15 State15 AR15 AM15 17
$60,678
City16 State16 AR16 AM16 18
$51,879
City17 State17 AR17 AM17 19
$54,253
City18 State18 AR18 AM18 20
$66,047
City19 State19 AR19 AM19 21
$68,186
City20 State20 AR20 AM20 22
$63,863
City21 State21 AR21 AM21 23
$62,101
City22 State22 AR22 AM22
Does that help?
Try
Book5 A B C D E F G H I J K L M N O P Q R S T U V W 1 1 Location Average Income City State Average Rent Average Mortgate 0-49000 50,000-99,999 2 2 1 45,231 City1 State1 AR1 AM1 2 1 45,231 City1 State1 AR1 AM1 4 3 69827 City3 State3 AR3 AM3 3 3 2 108,634 City2 State2 AR2 AM2 5 4 71567 City4 State4 AR4 AM4 4 4 3 69,827 City3 State3 AR3 AM3 6 5 78763 City5 State5 AR5 AM5 5 5 4 71,567 City4 State4 AR4 AM4 7 7 72613 City6 State6 AR6 AM6 6 6 5 78,763 City5 State5 AR5 AM5 8 8 64444 City7 State7 AR7 AM7 7 7 7 72,613 City6 State6 AR6 AM6 9 9 58744 City8 State8 AR8 AM8 8 8 8 64,444 City7 State7 AR7 AM7 10 10 55995 City9 State9 AR9 AM9 9 9 9 58,744 City8 State8 AR8 AM8 11 11 59230 City10 State10 AR10 AM10 10 10 10 55,995 City9 State9 AR9 AM9 12 12 77989 City11 State11 AR11 AM11 11 11 11 59,230 City10 State10 AR10 AM10 13 13 61778 City12 State12 AR12 AM12 12 12 12 77,989 City11 State11 AR11 AM11 14 14 60383 City13 State13 AR13 AM13 13 13 13 61,778 City12 State12 AR12 AM12 15 15 55867 City14 State14 AR14 AM14 14 14 14 60,383 City13 State13 AR13 AM13 16 16 57992 City15 State15 AR15 AM15 15 15 15 55,867 City14 State14 AR14 AM14 17 17 60678 City16 State16 AR16 AM16 16 16 16 57,992 City15 State15 AR15 AM15 18 18 51879 City17 State17 AR17 AM17 17 17 17 60,678 City16 State16 AR16 AM16 19 19 54253 City18 State18 AR18 AM18 18 18 18 51,879 City17 State17 AR17 AM17 20 20 66047 City19 State19 AR19 AM19 19 19 19 54,253 City18 State18 AR18 AM18 21 21 68186 City20 State20 AR20 AM20 20 20 20 66,047 City19 State19 AR19 AM19 22 22 63863 City21 State21 AR21 AM21 21 21 21 68,186 City20 State20 AR20 AM20 23 23 62101 City22 State22 AR22 AM22 22 22 22 63,863 City21 State21 AR21 AM21 23 23 23 62,101 City22 State22 AR22 AM22 24 24 24 175,000 City23 State23 AR23 AM23 25
Thanks fr that.
How about
Fluff.xlsm A B C D E F G H I J K L 1 Customer Segment 1 ($0-$49,000) Customer Segment 2 ($50,000-$99,999) 2 Location Average Income City State Average Rent Average Mortgate Location Average Income City State Average Rent Average Mortgate 3 1 45231 City1 State1 AR1 AM1 3 69827 City3 State3 AR3 AM3 4 4 71567 City4 State4 AR4 AM4 5 5 78763 City5 State5 AR5 AM5 6 7 72613 City6 State6 AR6 AM6 7 8 64444 City7 State7 AR7 AM7 8 9 58744 City8 State8 AR8 AM8 9 10 55995 City9 State9 AR9 AM9 10 11 59230 City10 State10 AR10 AM10 11 12 77989 City11 State11 AR11 AM11 12 13 61778 City12 State12 AR12 AM12 13 14 60383 City13 State13 AR13 AM13 14 15 55867 City14 State14 AR14 AM14 15 16 57992 City15 State15 AR15 AM15 16 17 60678 City16 State16 AR16 AM16 17 18 51879 City17 State17 AR17 AM17 18 19 54253 City18 State18 AR18 AM18 19 20 66047 City19 State19 AR19 AM19 20 21 68186 City20 State20 AR20 AM20 21 22 63863 City21 State21 AR21 AM21 22 23 62101 City22 State22 AR22 AM22 23
Thanks fr that.
How about
Fluff.xlsm A B C D E F G H I J K L 1 Customer Segment 1 ($0-$49,000) Customer Segment 2 ($50,000-$99,999) 2 Location Average Income City State Average Rent Average Mortgate Location Average Income City State Average Rent Average Mortgate 3 1 45231 City1 State1 AR1 AM1 3 69827 City3 State3 AR3 AM3 4 4 71567 City4 State4 AR4 AM4 5 5 78763 City5 State5 AR5 AM5 6 7 72613 City6 State6 AR6 AM6 7 8 64444 City7 State7 AR7 AM7 8 9 58744 City8 State8 AR8 AM8 9 10 55995 City9 State9 AR9 AM9 10 11 59230 City10 State10 AR10 AM10 11 12 77989 City11 State11 AR11 AM11 12 13 61778 City12 State12 AR12 AM12 13 14 60383 City13 State13 AR13 AM13 14 15 55867 City14 State14 AR14 AM14 15 16 57992 City15 State15 AR15 AM15 16 17 60678 City16 State16 AR16 AM16 17 18 51879 City17 State17 AR17 AM17 18 19 54253 City18 State18 AR18 AM18 19 20 66047 City19 State19 AR19 AM19 20 21 68186 City20 State20 AR20 AM20 21 22 63863 City21 State21 AR21 AM21 22 23 62101 City22 State22 AR22 AM22 23
You guys are seriously amazing! Thank you
Glad we could help & thanks for the feedback.
Threads
1,214,970
Messages
6,122,514
Members
449,088
Latest member
RandomExceller01
