ignoring blank cells in an array forumla

E

ENAPo

New Member
Joined
May 16, 2013
Messages
23
I have this formula in a cell:
=IF(ISERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5)),"",INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5))

If the cell it's pulling the information from is blank it puts a 0 in the cell. I want it to just leave it blank if the cell is blank but am at a loss where to put that in the formula. Hope that doesn't sound confusing.

Thanks!!
 
Sort by date Sort by votes
ENAPo said:
It is super secret. :)
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]6
[/TD]
[TD]10/31/2013
[/TD]
[TD]14578
[/TD]
[TD]Butter County
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]10/16/2013
[/TD]
[TD]15489
[/TD]
[TD]Cream County
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10/6/2013
[/TD]
[TD]45879
[/TD]
[TD]Cheese County
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]11/02/2013
[/TD]
[TD]45684
[/TD]
[TD]Butter County
[/TD]
[/TR]
</TBODY>[/TABLE]







This is what I need to do and I hope I explain it right. I have a large amount of information with 15 columns and up to 2000+ rows, that I paste/import into Excel from Quickbooks. I have a drop down box that lists products that coincides with the numbers in the above table. (6,3...). When I select a product from the drop down menu, I want it to populate all the information in my 2000+ rows that equals the number into another section of the worksheet. Ideally, I would like a new worksheet separate from the main information. For instance, #6 is populated in cell Q1 when I select the product from the drop down menu. So in another worksheet or area of table, I want everything in that row that equals 6 to be populated and however many rows has 6. Does that make any sense? I basically want a whole other table that filters for only the item that I have selected. I need to show this to others and I would rather not have them mess with filtering. I would be REALLY nice if I can have it filter based on the month that is populated in A1 as well. For instance, only give me every line that equals 6 and is in the month of October. I am really new to all these formulas in excel and am trying my best. Not trying to be aloof.
Click to expand...

Sheet1, A:D, houses the relevant data.

[TABLE="width: 442"]
<TBODY>[TR]
[TD="class: xl65, width: 106, bgcolor: white"]Product
[/TD]
[TD="class: xl66, width: 187, bgcolor: white"]Date
[/TD]
[TD="class: xl65, width: 112, bgcolor: white"]Code
[/TD]
[TD="class: xl65, width: 185, bgcolor: white"]Location
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 106, bgcolor: white"]6
[/TD]
[TD="class: xl68, width: 187, bgcolor: white"]10/31/2013
[/TD]
[TD="class: xl67, width: 112, bgcolor: white"]14578
[/TD]
[TD="class: xl67, width: 185, bgcolor: white"]Butter County
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 106, bgcolor: white"]3
[/TD]
[TD="class: xl68, width: 187, bgcolor: white"]10/16/2013
[/TD]
[TD="class: xl67, width: 112, bgcolor: white"]15489
[/TD]
[TD="class: xl67, width: 185, bgcolor: white"]Cream County
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 106, bgcolor: white"]2
[/TD]
[TD="class: xl68, width: 187, bgcolor: white"]10/6/2013
[/TD]
[TD="class: xl67, width: 112, bgcolor: white"]45879
[/TD]
[TD="class: xl67, width: 185, bgcolor: white"]Cheese County
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 106, bgcolor: white"]6
[/TD]
[TD="class: xl68, width: 187, bgcolor: white"]11/2/2013
[/TD]
[TD="class: xl67, width: 112, bgcolor: white"]45684
[/TD]
[TD="class: xl67, width: 185, bgcolor: white"]Butter County
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 106, bgcolor: white"]2
[/TD]
[TD="class: xl68, width: 187, bgcolor: white"]11/21/2013
[/TD]
[TD="class: xl67, width: 112, bgcolor: white"]45953
[/TD]
[TD="class: xl67, width: 185, bgcolor: white"]Cheese County
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 106, bgcolor: white"]6
[/TD]
[TD="class: xl68, width: 187, bgcolor: white"]11/21/2013
[/TD]
[TD="class: xl67, width: 112, bgcolor: white"]45785
[/TD]
[TD="class: xl67, width: 185, bgcolor: white"]Butter County
[/TD]
[/TR]
</TBODY>[/TABLE]

Sheet2, A:E, houses the processing...

[TABLE="width: 373"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3157" width=89><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3271" width=92><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6144" width=173><TBODY>[TR]
[TD="class: xl68, width: 64, bgcolor: #ebf1de"]Nov[/TD]
[TD="class: xl65, width: 89, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 79, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 92, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 173, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64, bgcolor: #ebf1de"]6[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]Idx[/TD]
[TD="class: xl66, width: 89, bgcolor: white"]Product[/TD]
[TD="class: xl67, width: 79, bgcolor: white"]Date[/TD]
[TD="class: xl66, width: 92, bgcolor: white"]Code[/TD]
[TD="class: xl66, width: 173, bgcolor: white"]Location[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]41580[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45684[/TD]
[TD="class: xl65, bgcolor: transparent"]Butter County[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]41599[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45785[/TD]
[TD="class: xl65, bgcolor: transparent"]Butter County[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

A1: Mar (a month of interest, given as a literal month name)

A2: 6 (a product of interest, selected from the dropdown list in this cell)

A5, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(SMALL(IF(Sheet1!$A$2:$A$7=$A$2,
  IF(Sheet1!$B$2:$B$7-DAY(Sheet1!$B$2:$B$7)+1=(1&$A$1)+0,
  ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1)),ROWS($A$5:A5)),"")

B5, just enter, copy across as far as needed, and down:
Rich (BB code): 
=IF($A5="","",INDEX(Sheet1!A$2:A$7,$A5))

Note 1. If so desired, A1 can house a true date like 1-Nov-13, custom-formatted either Nov-13 or Nov.
Note 2. If so desired, the references to the data area can be set up as references to dynamic named ranges.
 
Last edited:
Upvote 0
Hercules1946 said:
Sorry - I think I get it now :).
I put my own data into the cells you've used in order to demonstrate the different results?. I'm afraid I'm good at missing the obvious sometimes..........

Thanks very much for your help.

Hercules
Click to expand...

You're welcome.

Markmzz
 
Upvote 0
Another way:

Layout

[TABLE="width: 655"]
<colgroup><col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" span="11"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <tbody>[TR]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header01[/TD]
[TD="class: xl65, width: 63, bgcolor: transparent"]Header02[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header03[/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"]Header000004[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header05[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header06[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header07[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header08[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header09[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header10[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header11[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header12[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header13[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header14[/TD]
[TD="class: xl65, width: 53, bgcolor: transparent"]Header15[/TD]
[TD="class: xl68, width: 40, bgcolor: transparent"]Sheet1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]31/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]14578[/TD]
[TD="class: xl66, bgcolor: transparent"]Butter County[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]95[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]73[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]16/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]15489[/TD]
[TD="class: xl66, bgcolor: transparent"]Cream County[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]75[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]84[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]42[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]59[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]06/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]45879[/TD]
[TD="class: xl66, bgcolor: transparent"]Cheese County[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]93[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]57[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]93[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]68[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]64[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]81[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]06/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]45684[/TD]
[TD="class: xl66, bgcolor: transparent"]Cheese County[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]53[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]72[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]93[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]99[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]78[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]16/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]14578[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]86[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]58[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]87[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]80[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]67[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]97[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]93[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]16/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]15489[/TD]
[TD="class: xl66, bgcolor: transparent"]Cream County[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]80[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]59[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]59[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]96[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]82[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]94[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]77[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]94[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]06/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]45879[/TD]
[TD="class: xl66, bgcolor: transparent"]Cheese County[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]69[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]65[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]93[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]85[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]59[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]70[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]02/11/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]45684[/TD]
[TD="class: xl66, bgcolor: transparent"]Butter County[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]80[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]88[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]56[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]67[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]76[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]30/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]43711[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]59[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]90[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]66[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]64[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]51[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]73[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]85[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 662"]
<colgroup><col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" span="3"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" span="11"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <tbody>[TR]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header01[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header02[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header03[/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"]Header000004[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header05[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header06[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header07[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header08[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header09[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header10[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header11[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header12[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header13[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header14[/TD]
[TD="class: xl66, width: 54, bgcolor: transparent"]Header15[/TD]
[TD="class: xl67, width: 40, bgcolor: transparent"]Sheet2[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10/2013[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]14578[/TD]
[TD="class: xl68, bgcolor: yellow"]Butter County[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]49[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]49[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]29[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]33[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]95[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]46[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]15[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]56[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]29[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]27[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]73[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]45684[/TD]
[TD="class: xl68, bgcolor: yellow"]Cheese County[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]56[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]53[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]18[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]35[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]72[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]93[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]99[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]78[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]14578[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]86[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]58[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]87[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]80[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]36[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]67[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]97[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]49[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]23[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]93[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]27[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]37029[/TD]
[TD="class: xl68, bgcolor: yellow"]Cheese County[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]91[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]22[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]59[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]28[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]50[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]86[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]71[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]60[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]36[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]21[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]24[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]29870[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]77[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]88[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]40[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]13[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]88[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]67[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]36[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]82[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]80[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]91[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]13[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]53955[/TD]
[TD="class: xl68, bgcolor: yellow"]Cheese County[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]27[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]74[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]61[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]93[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]35[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]43[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]12[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]20[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]40[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]37[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]16[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]25052[/TD]
[TD="class: xl68, bgcolor: yellow"]Cream County[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]93[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]66[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]11[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]69[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]98[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]95[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]83[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]81[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]75[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]34[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]24[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]34201[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]37[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]45[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]61[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]86[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]69[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]13[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]81[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]82[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]13[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]21[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]49[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]30406[/TD]
[TD="class: xl68, bgcolor: yellow"]Cheese County[/TD]
[TD="class: xl68, bgcolor: yellow"] [/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]36[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]44[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]90[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]60[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]89[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]55[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]26[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]96[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]58[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]60[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


Formula

Code: 
In C2 (in Sheet2) - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(IF(Sheet1!C$2:C$2500="","",Sheet1!C$2:C$2500),
SMALL(IF(Sheet1!$A$2:$A$2500=$A$2,IF(TEXT(Sheet1!$B$2:$B$2500,"mmyy")=
TEXT($B$2,"mmyy"),ROW(Sheet1!$A$2:$A$2500)-ROW(Sheet1!$A$2)+1)),ROWS(C$2:C2))),"")

And copy to the right and down.


Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Return non-blank cells in an array to a single column
Replies
3
Views
773
Peter_SSs
Peter_SSs
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
892
felixstraube
F
Asbestos_Jen
  • Solved
Speed up slow array index calculations
Replies
3
Views
181
Scott Huish
Scott Huish
edge37
  • Solved
Autonumbering formula that ignores empty cells containing formulas and consider them blank
2
Replies
16
Views
448
Fluff
Fluff
zookeepertx
  • Question Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
489
zookeepertx
zookeepertx

Share this page

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