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

Run-time error '50290': Application-defined or object-defined error Excel
VBA: saving to specific path based on cell values
how to extract only specific strings in VBA
Excel VBA: Communicating via named pipe
macro inserts the word TRUE in cell
Output difference of two columns excel with exclusion criteria
Excel VBA code reads wrong innerHTML code
Get the length of decimal places
Excel Formula to copy cell value to a column based on condition
Excel Webservice Function Yahoo Finance
Using a dynamic cell value in excel VBA
Excel VBA gives type mismatch on dividing decimal numbers after split
Get data from a website table to the excel sheet
VBA Set Dropdown value
VBA Access to Excel export, Error 1004 and 70 when re-running the script
Excel 2010 if condition occurs in column

Categories

HOME
gnuplot
powershell-v4.0
https
set
bloomberg
attributes
g2o
maven-lifecycle
instagram-api
bigdata
axon
web-component
libcurl
cloudify
cdb
apache-felix
touch
bug-tracking
python-datetime
floyd-warshall
remote-connections
angular-openlayers
uppaal
jointjs
webm
kinvey
required
uppercase
slamdata
state
free-jqgrid
web.py
james
data-storage
pivottable.js
grayscale
xilinx-edk
automated-deployment
gulp-usemin
google-truth
autocorrect
ranorex
rethinkdb-python
smo
cakephp-3.3
showdown
sift
autocompleteextender
bubble-sort
slug
jquery-tags-input
gyp
google-chrome-arc
code-signing-entitlements
jake
azure-webjobssdk
vibration
gio
android-image
ucp
rational-team-concert
ubuntu-15.10
liquibase-hibernate
backspace
realbasic
xcode-bots
apache-commons
xamarin-forms
mpmediapickercontroller
event-loop
code-completion
android-view
slab
google-plugin-eclipse
ember.js-view
nools
factoring
updatemodel
resourcedictionary
phpmotion
harfbuzz
wsimport
websitespark
imagecreatefrompng
emacs-dirtree
converters
visual-studio-test-runner
jlayer
abpeoplepickerview
wcsf
cloning
layar
iasyncresult
dft
rakefile
clean-language
showmodaldialog
itsm
table-driven

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