Auto Summary from non Blank cells

[jason8490]

Hi all

I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 I want to create an auto summary or something.
I've googled and googled and just cant find anything.
Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.

Also I am using Excel 2010.

 

[Aladin Akyurek]

Hi all

I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 I want to create an auto summary or something.
I've googled and googled and just cant find anything.
Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.

Also I am using Excel 2010.

Try to post a 10-row illustrative data along with the results you want to see.

 

[jason8490]

...A.....B.... C.... D.... E.... F.... G.... H.... I.... J
...ID.... type.... Value.... Bid.... Overbid.... ytd.... LTV
2 .11
3 .12............... 100
4 .13
5 .14
6 .15
7 .16.....RESD.................................................. 8%
8 .17
9 .18..........................1000
10 .19


I want to pick up the info that has changed between this weeks and last weeks sheets.
So if those values popped up thats because someone input something wrong or there was an adjustment.
Theres lots of blanks.

 

[jason8490]

I've been messing around with something like =IF(ROWS($1:1)<=COUNTIF(Add_File!$BC$2:$BC$20000,1),INDEX(Add_File!$AW$2:$AW$20000,SMALL(IF(Add_File!$BC$2:$BC$20000=1,ROW(Add_File!$BC$2:$BC$20000)-ROW(Add_File!$BC$2)+1),ROWS($1:1))),"")

 

[Aladin Akyurek]

...A.....B.... C.... D.... E.... F.... G.... H.... I.... J
...ID.... type.... Value.... Bid.... Overbid.... ytd.... LTV
2 .11
3 .12............... 100
4 .13
5 .14
6 .15
7 .16.....RESD.................................................. 8%
8 .17
9 .18..........................1000
10 .19


I want to pick up the info that has changed between this weeks and last weeks sheets.
So if those values popped up thats because someone input something wrong or there was an adjustment.
Theres lots of blanks.

I've been messing around with something like =IF(ROWS($1:1)<=COUNTIF(Add_File!$BC$2:$BC$20000,1),INDEX(Add_File!$AW$2:$AW$20000,SMALL(IF(Add_File!$BC$2:$BC$20000=1,ROW(Add_File!$BC$2:$BC$20000)-ROW(Add_File!$BC$2)+1),ROWS($1:1))),"")

It's hard to understand the exhibit you posted. A better exhibit would be better, along with the concrete results you want to see.

 

[jason8490]

It's hard to understand the exhibit you posted. A better exhibit would be better, along with the concrete results you want to see.

Comparison Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ID[/TD]
[TD]Address[/TD]
[TD]value[/TD]
[TD]Bid[/TD]
[TD]Overbid[/TD]
[TD]ytd[/TD]
[TD]LTV[/TD]
[TD]Row Blank Count[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12[/TD]
[TD]1235 Maple St.[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]1%[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]17[/TD]
[TD]555 Jefferson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Summery Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Changed Value 1[/TD]
[TD]Changed Value 2[/TD]
[TD]Changed Value 3[/TD]
[TD]Changed Value 4 [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Address[/TD]
[TD]Bid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]YTD[/TD]
[TD]LTV[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Overbid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope this helps a bit more. The Comparison sheet compares two sheets in the same book they show the same headings and number of rows and so on so fourth. The comparison does a =IF(VLOOKUP($B7,'CurrentCopy'!$A$5:$BA$20000,6,0)<>VLOOKUP($B7,'PreviousCopy'!$A$5:$BA$20000,6,0),VLOOKUP($B7,'CurrentCopy!$A$5:$BA$20000,6,0),"") so you see it returns blanks if there has been no change. But i want the summary to return the ID number that has had a change and tell me which column there has been a discrepancy in so I dont need to search thru or filter thru all the blanks.

 

[jason8490]

help please??? lol