excel


Excel - Export cells identified by countif to new file


I'm trying to export cells identified by countif to a new file.
For example, given:
Red dog
Blue cat
Red horse
Purple bird
Red mouse
I can get countif to count the number of times Red occurs in column A.
But how can I have excel write the contents of Column A and B to a new file (csv?) only if Column A is Red?
So the output would be
Red dog
Red horse
Red mouse
In this example, I could manually sort this list and copy it, but my actual conutif statement (technically countifs) has 4 or 5 variables.
Thanks,
Avram
Probably a more elegant solution, but this will work.
Add a helper column that will be true or false depending if the row meets all of your criteria. This will generate a table similar to following
Red Dog TRUE
Blue Cat FALSE
Red Horse TRUE
Purple Bird FALSE
Red Mouse TRUE
Then, a simple macro will copy and rows with true to a new sheet. Edit as needed (not necessarily the most elegant, but gets the job done)
Sub copyCriteriaRange()
Dim rcounter As Integer, outputRow As Integer, dataVariant As Variant
outputRow = 1
'loop through all rows
For rcounter = 1 To 5
'if column 3 is true, copy to a new sheet
If Sheets("Sheet1").Cells(rcounter, 3) = True Then
dataVariant = Sheets("Sheet1").Range("A" & rcounter & ":C" & rcounter)
Sheets("Sheet2").Range("A" & outputRow & ":C" & outputRow) = dataVariant
outputRow = outputRow + 1
End If
Next
'now get rid of helper column
Sheets("Sheet2").Range("C:C").ClearContents
MsgBox "Done copying"
End Sub
Then can use another macro to export to csv. Should be easy enough to find one through Google. Enjoy!
For formulas:
In another sheet in A1 put the desired test, in this case "Red". In A2 put this formula:
=IF(ROW()<=COUNTIF(Sheet8!$A$1:$A$5,$A$1),$A$1,"")
And copy down as many rows as desired.
In B1 put this array formula:
=IF(A1<>"",INDEX(Sheet8!$B$1:$B$5,LARGE(ROW($1:$5)*ISNUMBER(FIND(A1,Sheet8!$A$1:$A$5)),COUNTA($A$1:$A1))),"")
Change all Sheet8 references to the name of the sheet that holds the data. To enlarge the data being searched fix the ranges Sheet8!$B$1:$B$5 and Sheet8!$A$1:$A$5 to match the size. As well as the ROW($1:$5) needs to include the same number of rows of data.
Confirm with Ctrl-Shift-Enter and copy down.
For a UDF that you can use as a function:
Function Avram(val As String, IRng As Range, k As Long)
Dim rng
Dim j As Long
Dim i As Long
rng = IRng.Value
j = 1
For i = LBound(rng, 1) To UBound(rng, 1)
If rng(i, 1) = val Then
If j = k Then
Avram = rng(i, 2)
Exit Function
Else
j = j + 1
End If
End If
Next i
Avram = CVErr(xlErrNA)
End Function
This wold go in a module attached to the workbook (Not the workbook or worksheet code)
You would enter Column A on the sheet as stated in the formula part above. Then in B1 you would enter:
=IFERROR(Avram(A1,Sheet8!$A$1:$B$5,COUNTA($A$1:$A1)),"")
This time the only thing needing change is the Sheet8!$A$1:$B$5 to include your range of data. This is less finicky than the array formula and faster.
As for a Sub to do it all then:
Sub avram2()
Dim ows As Worksheet
Dim tws As Worksheet
Dim rng
Dim Orng
Dim i As Long
Dim FndString As String
FndString = "Red" 'Change to what you want
Set ows = Sheets("Sheet8") 'Change to your sheet name with the data.
Set tws = Sheets("Sheet9") 'Change to the output sheet name
With ows
rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 2).End(xlUp)).Value
End With
For i = LBound(rng, 1) To UBound(rng, 1)
If rng(i, 1) = FndString Then
tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2).Value = Array(rng(i, 1), rng(i, 2))
End If
Next i
End Sub

Related Links

How to reference cell selections for hyperlinks?
Excel to voice cells' value whenever it is selected with VBA
ImportXML slow while importing data in excel table
Using VBA to Add Rows based on a cell value onto another worksheet
Excel PivotTable for counting words for female and male
Filter a PivotChart with Dates
Combobox drop down showing up in other sheets
Excel Marco set range.address
Excel VBA to update only one cell at one time
Excel VBA - Refer to a worksheet as a variable in an IF statement
Excel Formula to Lookup multiple columns
VBA: unable to get a range from search
Searching occurrence of custom codes in data set
Custom Eval function in excel
Implementing a status bar window in VBA Excel script
Copy, Paste & Sort non-defined ranges “Object variable or With block variable not set”

Categories

HOME
web
neural-network
java-ee
https
mono
google-speech-api
sharppcap
authorization
ipad
uiviewcontroller
unity5
linuxmint
gemfire
javacard
spring-transactions
translate
jasperserver
google-play-games
adodb-php
filenet-p8
jxmaps
subplot
atlassian-sourcetree
hudson
tv
size-classes
na
white
openfiledialog
sublimerepl
yamldotnet
tail-recursion
web.py
data-storage
shieldui
lift
predict
3d-modelling
devexpress-wpf
fixture
azure-sql-server
singleton-type
multibox
ssas-tabular
simplemodal
ffi
exceljs
wistia
on-screen-keyboard
jquery-events
google-url-shortener
bubble-sort
nsoutlineview
ldflags
metamodel
reportviewer2008
ilmerge
dex2oat
mds
page-load-time
ionic-view
mediafire
angular-component-router
dsquery
zurb-foundation-5
beta-testing
gstat
acronym
jison
soundex
srv
iec61131-3
ember-qunit
jml
knockout-mapping-plugin
start-stop-daemon
bitmapimage
rails-i18n
mashup
worklight-studio
apiaxle
parallel.for
texmacs
c++builder-6
fubumvc
gideros
graphml
dynamic-language-runtime
cgimagesource
customvalidator
django-sessions
hibernate-annotations
cocos2d-python
eda
fql.multiquery
openflashchart2
live-cd
remote-administration

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App