Conditional Formatting and sumif or index or another formula? (please help!)

[aqalna]

<article>

I am working on a file and still need help for the following:

Is there a way to retrieve the information for "rows 16 to 23" under column "B" from where it populates the data based on "rows 1 to 14" under column "B"

Lastly, I am trying to figure out how can I highlight the "row 22" (Location 7) in yellow since it does not exist anymore from "rows 8 to 14"

As well, similar to "row 23" (location 8) where it is newly added and is not shown in "rows 8 to 14" so I want that to be highlighted in light blue.

Let me know if anyone can teach me in doing that please.

Here is my data set:
Column A Column B Column C Column D Column E



[TABLE="width: 941"]
<colgroup><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Areas[/TD]
[TD="colspan: 4"] types [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] school [/TD]
[TD] car [/TD]
[TD] food [/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]row 1[/TD]
[TD]Location1[/TD]
[TD] 150.00[/TD]
[TD] 500.00[/TD]
[TD] 200.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 2[/TD]
[TD]Location2[/TD]
[TD] 900.00[/TD]
[TD] 450.00[/TD]
[TD] 325.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 3[/TD]
[TD]Location3[/TD]
[TD] 436.00[/TD]
[TD] 543.00[/TD]
[TD] 764.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 4[/TD]
[TD]Location4[/TD]
[TD] 8,565.00[/TD]
[TD] 2,342.00[/TD]
[TD] 4,635.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 5[/TD]
[TD]Location5[/TD]
[TD] 967.00[/TD]
[TD] 876.00[/TD]
[TD] 65.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 6[/TD]
[TD]Location6[/TD]
[TD] 8,565.00[/TD]
[TD] 2,342.00[/TD]
[TD] 123.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
[TR]
[TD]row 7[/TD]
[TD]Location7[/TD]
[TD] 904,345.00[/TD]
[TD] 8,765.00[/TD]
[TD] 6,546.00[/TD]
[TD]4/9/2016[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 792"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]row 8[/TD]
[TD]Location3[/TD]
[TD] 234.00[/TD]
[TD] 342.00[/TD]
[TD] 657.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 9[/TD]
[TD]Location1[/TD]
[TD] 554.00[/TD]
[TD] 8,797.00[/TD]
[TD] 23,423.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 10[/TD]
[TD]Location6[/TD]
[TD] 234.00[/TD]
[TD] 34,576.00[/TD]
[TD] 75.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 11[/TD]
[TD]Location4[/TD]
[TD] 967.00[/TD]
[TD] 6,456.00[/TD]
[TD] 655,464.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 12[/TD]
[TD]Location2[/TD]
[TD] 905.00[/TD]
[TD] 873.00[/TD]
[TD] 1,232.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 13[/TD]
[TD]Location5[/TD]
[TD] 2,347.00[/TD]
[TD] 345.00[/TD]
[TD] 4,546.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
[TR]
[TD]row 14[/TD]
[TD]Location8
[/TD]
[TD] 5,000.00[/TD]
[TD] 4,000.00[/TD]
[TD] 2,000.00[/TD]
[TD]4/16/2016[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 502"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 611"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]row 15[/TD]
[TD]Variance Date[/TD]
[TD]4/9/2016[/TD]
[TD]4/16/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]


Column A

[/TD]
[TD] Column B
[/TD]
[TD] Column C
[/TD]
[TD] Colum D
[/TD]
[TD] Column E
[/TD]
[/TR]
[TR]
[TD]row 16[/TD]
[TD]Location1[/TD]
[TD="align: right"]404.00
[/TD]
[TD="align: right"]8,297.00[/TD]
[TD="align: right"]23,223.00
[/TD]
[/TR]
[TR]
[TD]row 17[/TD]
[TD]Location2[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]423.00[/TD]
[TD="align: right"]907.00[/TD]
[/TR]
[TR]
[TD]row 18[/TD]
[TD]Location3[/TD]
[TD="align: right"]-202.00[/TD]
[TD="align: right"]-201.00[/TD]
[TD="align: right"]-107.00[/TD]
[/TR]
[TR]
[TD]row 19[/TD]
[TD]Location4[/TD]
[TD="align: right"]-7,598.00
[/TD]
[TD="align: right"]4,114.00[/TD]
[TD="align: right"]650,829.00[/TD]
[/TR]
[TR]
[TD]row 20[/TD]
[TD]Location5[/TD]
[TD="align: right"]1,380.00[/TD]
[TD="align: right"]-531.00[/TD]
[TD="align: right"]4,481.00[/TD]
[/TR]
[TR]
[TD]row 21[/TD]
[TD]Location6[/TD]
[TD="align: right"]-8,331.00[/TD]
[TD="align: right"]32,234.00[/TD]
[TD="align: right"]-48.00[/TD]
[/TR]
[TR]
[TD]row 22[/TD]
[TD]Location7[/TD]
[TD="align: right"]-904,345.00[/TD]
[TD="align: right"]-8,765.00[/TD]
[TD="align: right"]-6,546.00[/TD]
[/TR]
[TR]
[TD]row 23[/TD]
[TD]Location8[/TD]
[TD="align: right"]5,000.00[/TD]
[TD="align: right"]4,000.00[/TD]
[TD="align: right"]2,000.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


​

</article>


​

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dachickenMind]

Excel 2011 32 bit
	A	B	C	D	E
1	Location1	150	500	200	4/9/16
2	Location2	900	450	325	4/9/16
3	Location3	436.00	543.00	764	4/9/16
4	Location4	8,565.00	2,342.00	4,635.00	4/9/16
5	Location5	967.00	876.00	65	4/9/16
6	Location6	8,565.00	2,342.00	123	4/9/16
7	Location7	904,345.00	8,765.00	6,546.00	4/9/16
8	Location3	234	342	657	4/16/16
9	Location1	554	8,797.00	23,423.00	4/16/16
10	Location6	234	34,576.00	75	4/16/16
11	Location4	967	6,456.00	655,464.00	4/16/16
12	Location2	905	873	1,232.00	4/16/16
13	Location5	2,347.00	345	4,546.00	4/16/16
14	Location8	5,000.00	4,000.00	2,000.00	4/16/16
15
16	Location1	404	8,297.00	23,223.00
17	Location2	5	423	907
18	Location3	-202	-201	-107
19	Location4	-7,598.00	4,114.00	650,829.00
20	Location5	1,380.00	-531	4,481.00
21	Location6	-8,331.00	32,234.00	-48
22	Location7	-904,345.00	-8,765.00	-6,546.00
23	Location8	5,000.00	4,000.00	2,000.00
Sheet2

 

[dachickenMind]

Above is what I have in my table

 

[aqalna]

I should've organized my data that way...would've been easier to.understand!

Now you see in column A: i want to develop a formula for rows 16 to 23 under column A
Based on column A again but from rows 1 to 14 under column A

Above is what I have in my table

 

[dachickenMind]

Here is the formulas I put in the cells: Please ignore the second table.


Excel 2011 32 bit

	A	B	C	D	E
Location1
Location2
Location3
Location4
Location5
Location6
Location7
Location3
Location1
Location6
Location4
Location2
Location5
Location8
Location1
Location2
Location3
Location4
Location5
Location6
Location7
Location8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]900[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]436[/TD]
[TD="align: right"]543[/TD]
[TD="align: right"]764[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8565[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]4635[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]967[/TD]
[TD="align: right"]876[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8565[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]904345[/TD]
[TD="align: right"]8765[/TD]
[TD="align: right"]6546[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]234[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]657[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]554[/TD]
[TD="align: right"]8797[/TD]
[TD="align: right"]23423[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]234[/TD]
[TD="align: right"]34576[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]967[/TD]
[TD="align: right"]6456[/TD]
[TD="align: right"]655464[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]905[/TD]
[TD="align: right"]873[/TD]
[TD="align: right"]1232[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2347[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]4546[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A16,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A16,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A16,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A17,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A17,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A17,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A18,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A18,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A18,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A19,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A19,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A19,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A20,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A20,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A20,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A21,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A21,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A21,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A22,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A22,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A22,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A23,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A23,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A23,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B16[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A16,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C16[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A16,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D16[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A16,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B17[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A17,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C17[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A17,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D17[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A17,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B18[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A18,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C18[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A18,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D18[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A18,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B19[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A19,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C19[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A19,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D19[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A19,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B20[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A20,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C20[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A20,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D20[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A20,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B21[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A21,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C21[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A21,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D21[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A21,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B22[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A22,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C22[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A22,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D22[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A22,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B23[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A23,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C23[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A23,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]D23[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A23,$D$1:$D$14)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[aqalna]

I am not sure if your understanding my question but I am not looking for formulas in columns "B to D" instead I am focused in column "A" where I care in creating a formula under column "A" but for rows 16 to 23 that will be based on the data above in rows (1 to 14) under column "A" again.

I hope that makes it clearer in what I am trying to find.

Here is the formulas I put in the cells: Please ignore the second table.


Excel 2011 32 bit

	A	B	C	D	E
Location1
Location2
Location3
Location4
Location5
Location6
Location7
Location3
Location1
Location6
Location4
Location2
Location5
Location8
Location1
Location2
Location3
Location4
Location5
Location6
Location7
Location8

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]900[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]436[/TD]
[TD="align: right"]543[/TD]
[TD="align: right"]764[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8565[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]4635[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]967[/TD]
[TD="align: right"]876[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8565[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]904345[/TD]
[TD="align: right"]8765[/TD]
[TD="align: right"]6546[/TD]
[TD="align: right"]42469[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]234[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]657[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]554[/TD]
[TD="align: right"]8797[/TD]
[TD="align: right"]23423[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]234[/TD]
[TD="align: right"]34576[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]967[/TD]
[TD="align: right"]6456[/TD]
[TD="align: right"]655464[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]905[/TD]
[TD="align: right"]873[/TD]
[TD="align: right"]1232[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2347[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]4546[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]42476[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A16,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A16,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A16,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A17,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A17,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A17,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A18,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A18,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A18,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A19,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A19,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A19,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A20,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A20,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A20,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A21,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A21,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A21,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A22,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A22,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A22,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]=SUMIF($A$1:$A$14,A23,$B$1:$B$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A23,$C$1:$C$14)[/TD]
[TD="align: right"]=SUMIF($A$1:$A$14,A23,$D$1:$D$14)[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B16[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A16,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C16[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A16,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D16[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A16,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B17[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A17,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C17[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A17,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D17[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A17,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B18[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A18,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C18[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A18,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D18[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A18,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B19[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A19,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C19[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A19,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D19[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A19,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B20[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A20,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C20[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A20,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D20[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A20,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B21[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A21,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C21[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A21,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D21[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A21,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B22[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A22,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C22[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A22,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D22[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A22,$D$1:$D$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B23[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A23,$B$1:$B$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C23[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A23,$C$1:$C$14)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D23[/TH]
[TD="align: left"]=SUMIF($A$1:$A$14,A23,$D$1:$D$14)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dachickenMind]

Hi,
Use the below formula to build unique location in Column A. You need a array formula to build unique locations.

Excel 2011 32 bit

	A
Location
Location1
Location2
Location1
Location4
Location5
Location6
Location7
Location3
Location1
Location6
Location4
Location2
Location5
Location8
MyUniqueLocation
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0)))
=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0)))

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]

</tbody>

Myunique​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A18[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A19[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A20[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A21[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A22[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A23[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A24[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A25[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A26[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A27[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A28[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[dachickenMind]

Hi,
Use the above formula to build unique locations in Column A. You need an array formula to build unique locations. First, in A17, you need to input "MyUniqueLocation" as first keyword. You can type in any title, just do not have a same name like your location list. For example, you can not put location1 in the A17. We need this filed to avoid circle reference in the array formula. In A18, Please copy paste =IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))), Then,Control+Shift+Enter, the curved brackets will show in the formula indicating that is an array formula. Last step, drag down the formula as many row as you want. You got unique location list ' The second table also has comments to explain how to enter array formula.