Removing blank rows including those with "FALSE" data

brockk

brockk

Board Regular
Joined
Jul 1, 2006
Messages
170
Office Version
  1. 2013
Platform
  1. Web
ORIGINAL DATA WITH FALSE DATARESULT I'M GETTING USING THE FORMULADESIRED RESULT
GIG$94.99 GIG$94.99 GIG$94.99
XRXFree XRXFree XRXFreeFORMULA USED:
SND PLUS$69.99SND PLUS$69.99SND PLUS$69.99=IFERROR(INDEX($Q$4:$Q$49, SMALL(IF(FREQUENCY(IF($Q$4:$Q$49<>"", MATCH(ROW($Q$4:$Q$49), ROW($Q$4:$Q$49)), ""), MATCH(ROW($Q$4:$Q$49), ROW($Q$4:$Q$49)))>0, MATCH(ROW($Q$4:$Q$49), ROW($Q$4:$Q$49)), ""), ROW(A1)), COLUMN(A1)), "")
BBB$14.99 BBB$14.99 BBB$14.99
CDC$5.00CDC$5.00CDC$5.00
SWX - EIP$27.50SWX - EIP$27.50SWX - EIP$27.50
PPLAN$7.00PPLAN$7.00PPLAN$7.00
BF$23.20BF$23.20BF$23.20
FF$4.31FF$4.31FF$4.31
PF$59.99PF$59.99
PF$59.99HDR$21.98HDR$21.98
HDR$21.98DVR$54.95DVR$54.95
DVR$54.95DVRSFFALSEDVRSF$29.98
FALSEDVRPFALSEDVRP$9.99
FALSEXU$29.98XU$0.00
DVRSF$29.98XU - MULTIFALSEXU - MULTI$5.00
FALSEXU - OTP$9.99XU - OTP$60.00
DVRP$9.99XRX P$0.00XRX P$6.00
BUP$5.00BUP$60.00
EV1$60.00EV1$12.00
XU$0.00LV1$6.00LV1$12.00
XU - MULTI$5.00MAX$60.00MAX$15.00
XU - OTP$60.00Sho$12.00Sho$10.00
XRX P$6.00MGM$12.00MGM$6.00
BUP$60.00TMCFALSETMC$5.00
EV1$12.00Stz$15.00Stz$9.00
LV1$12.00FSP$10.00FSP$10.00
FALSEAMCFALSEAMC$7.99
MAX$15.00ATV- OTP1$6.00ATV- OTP1$150.00
Sho$10.00ATV- OTP2$5.00ATV- OTP2$360.00
FALSEATV- EIP1$9.00ATV- EIP1$5.00
MGM$6.00ATV- EIP2$10.00ATV- EIP2$8.00
TMC$5.00FALSE
Stz$9.00$7.99
FSP$10.00FALSE
FALSEFALSE
AMC$7.99$150.00
FALSE$360.00
FALSE$5.00
ATV- OTP1$150.00$8.00
ATV- OTP2$360.00
ATV- EIP1$5.00
ATV- EIP2$8.00


Greeting,

I am stumped at the moment.... I am trying to achieve a list from a dynamic range/array (shown as "Original Data with False data"), which depending on the selection may or may not have a FALSE outcome in the list. I am getting the results shown in ("Results I'm getting using the formula" list). I have provided the formula that I am using to see if this may help you tweak it so I may have the desired result. I need to remove the (blanks and/or FALSE statements), anything else like the word "Free" should remain as shown in the "Desired Result" list.

=IFERROR(INDEX($Q$4:$Q$49, SMALL(IF(FREQUENCY(IF($Q$4:$Q$49<>"", MATCH(ROW($Q$4:$Q$49), ROW($Q$4:$Q$49)), ""), MATCH(ROW($Q$4:$Q$49), ROW($Q$4:$Q$49)))>0, MATCH(ROW($Q$4:$Q$49), ROW($Q$4:$Q$49)), ""), ROW(A1)), COLUMN(A1)), "")
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Hi Brockk,

In Office 365, you could try this...
Excel Formula: 
=FILTER(C4:D46,(C4:C46<>"")*((D4:D46<>"")+(D4:D46<>FALSE)),"error")
Hope that helps,

Doug
 
Upvote 0
With XL365
Book1
AB
1GIG$94.99
2XRXFree
3SND PLUS$69.99
4BBB$14.99
5CDC$5.00
6SWX - EIP$27.50
7PPLAN$7.00
8BF$23.20
9FF$4.31
10
11PF$59.99
12HDR$21.98
13DVR$54.95
14FALSE
15FALSE
16DVRSF$29.98
17FALSE
18DVRP$9.99
19
20
21XU$0.00
Sheet1

Book1
GH
2GIG$94.99
3XRXFree
4SND PLUS$69.99
5BBB$14.99
6CDC$5.00
7SWX - EIP$27.50
8PPLAN$7.00
9BF$23.20
10FF$4.31
11PF$59.99
12HDR$21.98
13DVR$54.95
14DVRSF$29.98
15DVRP$9.99
16XU$0.00
Sheet1
Cell Formulas
RangeFormula
G2:H16G2=FILTER(A1:B21,A1:A21<>"")
Dynamic array formulas.
 
Upvote 0
Thank you so much for your replies to both Duggie33 and Arthurbr, but I completely forgot to mention that detail.... Although, I know it will work on 365, I completely forgot to mention that I would like to be able to use it on earlier versions of excel (2013 & 2016) to be exact as it will be used at work and I know that the FILTER function won't be recognized by this version. Yeah, I know.... they need to upgrade their version with all the modern changes. Could their be another formula that can accomplish wht I am needing to achieve? Thank you again.
 
Upvote 0
Might anyone know if this is a possibility to achieve on an earlier version of Excel,? I need it to work with at least the 2016 version. Thanks in advance.
 
Upvote 0
It looks like Column C contains unique values. If this is in fact the case, enter the following array formula in K5, copy down and over to the next column...

Excel Formula: 
=IFERROR(INDEX(C$5:C$47,AGGREGATE(15,6,(ROW($D$5:$D$47)-ROW($D$5)+1)/(($D$5:$D$47<>"")*($D$5:$D$47<>FALSE)),ROWS(K$2:K2))),"")

brockk.xlsm
ABCDEFGHIJKLM
1
2
3ORIGINAL DATA WITH FALSE DATARESULT I'M GETTING USING THE FORMULADESIRED RESULT
4
5GIG$94.99GIG$94.99GIG$94.99
6XRXFreeXRXFreeXRXFree
7SND PLUS$69.99SND PLUS$69.99SND PLUS$69.99
8BBB$14.99BBB$14.99BBB$14.99
9CDC$5.00CDC$5.00CDC$5.00
10SWX - EIP$27.50SWX - EIP$27.50SWX - EIP$27.50
11PPLAN$7.00PPLAN$7.00PPLAN$7.00
12BF$23.20BF$23.20BF$23.20
13FF$4.31FF$4.31FF$4.31
14PF$59.99PF$59.99
15PF$59.99HDR$21.98HDR$21.98
16HDR$21.98DVR$54.95DVR$54.95
17DVR$54.95DVRSFFALSEDVRSF$29.98
18FALSEDVRPFALSEDVRP$9.99
19FALSEXU$29.98XU$0.00
20DVRSF$29.98XU - MULTIFALSEXU - MULTI$5.00
21FALSEXU - OTP$9.99XU - OTP$60.00
22DVRP$9.99XRX P$0.00XRX P$6.00
23BUP$5.00BUP$60.00
24EV1$60.00EV1$12.00
25XU$0.00LV1$6.00LV1$12.00
26XU - MULTI$5.00MAX$60.00MAX$15.00
27XU - OTP$60.00Sho$12.00Sho$10.00
28XRX P$6.00MGM$12.00MGM$6.00
29BUP$60.00TMCFALSETMC$5.00
30EV1$12.00Stz$15.00Stz$9.00
31LV1$12.00FSP$10.00FSP$10.00
32FALSEAMCFALSEAMC$7.99
33MAX$15.00ATV- OTP1$6.00ATV- OTP1$150.00
34Sho$10.00ATV- OTP2$5.00ATV- OTP2$360.00
35FALSEATV- EIP1$9.00ATV- EIP1$5.00
36MGM$6.00ATV- EIP2$10.00ATV- EIP2$8.00
37TMC$5.00FALSE  
38Stz$9.00$7.99  
39FSP$10.00FALSE  
40FALSEFALSE  
41AMC$7.99$150.00  
42FALSE$360.00  
43FALSE$5.00  
44ATV- OTP1$150.00$8.00  
45ATV- OTP2$360.00  
46ATV- EIP1$5.00  
47ATV- EIP2$8.00  
48
Sheet1
Cell Formulas
RangeFormula
K5:L47K5=IFERROR(INDEX(C$5:C$47,AGGREGATE(15,6,(ROW($D$5:$D$47)-ROW($D$5)+1)/(($D$5:$D$47<>"")*($D$5:$D$47<>FALSE)),ROWS(K$2:K2))),"")


Although, the same thing can be made a bit more efficient...

J5:

Excel Formula: 
=COUNTIFS($D$5:$D$47,"<>",$D$5:$D$47,"<>FALSE")

K5, confirmed with CONTROL+SHIFT+ENTER, and copy down:

Excel Formula: 
=IF(ROWS(K$2:K2)<=$J$5,INDEX(C$5:C$47,AGGREGATE(15,6,(ROW($D$5:$D$47)-ROW($D$5)+1)/(($D$5:$D$47<>"")*($D$5:$D$47<>FALSE)),ROWS(K$2:K2))),"")

L5, copied down:

Excel Formula: 
=IF(K5<>"",VLOOKUP(K5,$C$5:$D$47,2,0),"")

brockk.xlsm
ABCDEFGHIJKLM
1
2
3ORIGINAL DATA WITH FALSE DATARESULT I'M GETTING USING THE FORMULADESIRED RESULT
4
5GIG$94.99GIG$94.9932GIG$94.99
6XRXFreeXRXFreeXRXFree
7SND PLUS$69.99SND PLUS$69.99SND PLUS$69.99
8BBB$14.99BBB$14.99BBB$14.99
9CDC$5.00CDC$5.00CDC$5.00
10SWX - EIP$27.50SWX - EIP$27.50SWX - EIP$27.50
11PPLAN$7.00PPLAN$7.00PPLAN$7.00
12BF$23.20BF$23.20BF$23.20
13FF$4.31FF$4.31FF$4.31
14PF$59.99PF$59.99
15PF$59.99HDR$21.98HDR$21.98
16HDR$21.98DVR$54.95DVR$54.95
17DVR$54.95DVRSFFALSEDVRSF$29.98
18FALSEDVRPFALSEDVRP$9.99
19FALSEXU$29.98XU$0.00
20DVRSF$29.98XU - MULTIFALSEXU - MULTI$5.00
21FALSEXU - OTP$9.99XU - OTP$60.00
22DVRP$9.99XRX P$0.00XRX P$6.00
23BUP$5.00BUP$60.00
24EV1$60.00EV1$12.00
25XU$0.00LV1$6.00LV1$12.00
26XU - MULTI$5.00MAX$60.00MAX$15.00
27XU - OTP$60.00Sho$12.00Sho$10.00
28XRX P$6.00MGM$12.00MGM$6.00
29BUP$60.00TMCFALSETMC$5.00
30EV1$12.00Stz$15.00Stz$9.00
31LV1$12.00FSP$10.00FSP$10.00
32FALSEAMCFALSEAMC$7.99
33MAX$15.00ATV- OTP1$6.00ATV- OTP1$150.00
34Sho$10.00ATV- OTP2$5.00ATV- OTP2$360.00
35FALSEATV- EIP1$9.00ATV- EIP1$5.00
36MGM$6.00ATV- EIP2$10.00ATV- EIP2$8.00
37TMC$5.00FALSE  
38Stz$9.00$7.99  
39FSP$10.00FALSE  
40FALSEFALSE  
41AMC$7.99$150.00  
42FALSE$360.00  
43FALSE$5.00  
44ATV- OTP1$150.00$8.00  
45ATV- OTP2$360.00  
46ATV- EIP1$5.00  
47ATV- EIP2$8.00  
48
Sheet2
Cell Formulas
RangeFormula
J5J5=COUNTIFS($D$5:$D$47,"<>",$D$5:$D$47,"<>FALSE")
K5:K47K5=IF(ROWS(K$2:K2)<=$J$5,INDEX(C$5:C$47,AGGREGATE(15,6,(ROW($D$5:$D$47)-ROW($D$5)+1)/(($D$5:$D$47<>"")*($D$5:$D$47<>FALSE)),ROWS(K$2:K2))),"")
L5:L47L5=IF(K5<>"",VLOOKUP(K5,$C$5:$D$47,2,0),"")


Hope this helps!
 
Upvote 1
Solution
That did the job! Thank you Domenic! That worked like a charm! I completely forgot and it never ocurred to me about the aggregate function.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match
2
Replies
10
Views
366
RapchikM
R
S
  • Solved
How to Rotate Rows to Columns with Large Data Set
Replies
8
Views
238
kevin9999
K
E
  • Solved
Summing Values
Replies
3
Views
218
LazyBug
LazyBug
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
194
ItalianPlatinum
I
montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012

Forum statistics

Threads
1,215,095
Messages
6,123,072
Members
449,093
Latest member
ripvw

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