Databases
StackOverflow2013
Postsdbo
POExcel VBA deleting rows that have mixed values for a given index
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA deleting rows that have mixed values for a given index
    primarykey
    data
    text
    <p>I have the following data</p> <pre><code>Name ID Value Alice 12C 500 Bob 14 60 Dan 15C 64 Dan 1C 25 Alice 4 556 Bob 11 455 </code></pre> <p>In my data, Alice has both numerical (4) and string+numerical ID (12C) and I want to delete all Alice rows, while I want to hold on to data of names where their ID is strictly numeric (Bob 11, 14) or strictly string+numeric (Dan 15C , 1C).</p> <p>First I make an array of unique Name entries:</p> <pre><code> FinalRow = 7 Name_column = 1 n = 1 Dim FID_Array() As Variant ReDim Preserve FID_Array(1 To 1) FID_Array(1) = Cells(2, Name_column) For j = 3 To FinalRow If Cells(j, Name_column).Value &lt;&gt; FID_Array(n) Then ReDim Preserve FID_Array(1 To n + 1) FID_Array(n + 1) = Cells(j, Name_column).Value n = n + 1 End If Next j </code></pre> <p>Then I make an Array of the row numbers that contain a particular Name</p> <pre><code>ReDim Preserve Count_FID_Array(1 To 1) As Variant n = 1 range_FID = A2:A7 ' In my actual code this is Range_FID ' range_FID = Cells(2, FolderId_column).Address &amp; ":" &amp; Cells(FinalRow, FolderId_column).Address For Each itm5 In FID_Array() Count_FID_Array(n) = Application.CountIf(" &amp; range_FID &amp; ", " &amp; itm5 &amp; ") ReDim Preserve Count_FID_Array(1 To n + 1) n = n + 1 Next itm5 </code></pre> <p>I don't think my CountIf is working. I have tried to store the value of Count_FID_Array in another cell in a different sheet but I am getting #value!</p> <p>If I got the countIf to work then I was going to sort the data by name, then double loop to check the ID variable the next "n" times to see if the last digit was "C" for all of them or to check if the ID was numeric for all of them.</p> <p>Can you please point out why my countif is not working and is there a smarter way to do this? </p> <p>I am using arrays of names here because in the end I want to feed the array into an autofilter and delete the rows that I don't want.</p> <p><strong>Update 1 3:45 PM Nov 21 2013: I have solved this as following:</strong></p> <p>I basically created three columns. First column was 0 or 1 depending on if the the ID was all numbers. The second column was 0 or 1 depending on if the last digit was "C" (in my real work the last two digits are "IB" ) and finally I compared the frequency of these occurences to the frequency of the Name itself. If any of those match then I give it the number 1 else 0. I use this index later to autofilter.</p> <p>Now I'll try to use zx8754's shorter formula in the VBA code and I will try to address the issues regarding Countif that Joe has raised.</p> <p>Sub conditionsforsubfolders()</p> <pre><code> FinalColumn = Cells(1, Columns.Count).End(xlToLeft).Column FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ActiveWorkbook.ActiveSheet.Columns(FinalColumn + 1).Insert ActiveWorkbook.ActiveSheet.Columns(FinalColumn + 2).Insert ActiveWorkbook.ActiveSheet.Columns(FinalColumn + 3).Insert Isnumber_Column = FinalColumn + 1 Is_IB_Column = FinalColumn + 2 Exceptions_Column = FinalColumn + 3 Cells(1, Isnumber_Column) = "Number" Cells(1, Is_IB_Column) = "Letters" Cells(1, Exceptions_Column) = "Exceptions" For j = 1 To FinalColumn If Cells(1, j).Value = "TradeId" Then TradeId_column = j ElseIf Cells(1, j).Value = "Total Notional per folder" Then Total_Notional_Per_Folder_Column = j ElseIf Cells(1, j).Value = "ExternalId" Then ExternalId_Column = j ElseIf Cells(1, j).Value = "FolderId" Then FolderId_column = j End If Next j range_FolderId_fixed = Cells(2, FolderId_column).Address &amp; ":" &amp; Cells(FinalRow, FolderId_column).Address range_TradeId_fixed = Cells(2, TradeId_column).Address &amp; ":" &amp; Cells(FinalRow, TradeId_column).Address range_Isnumber = Cells(2, Isnumber_Column).Address &amp; ":" &amp; Cells(FinalRow, Isnumber_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False) range_Isnumber_fixed = Cells(2, Isnumber_Column).Address &amp; ":" &amp; Cells(FinalRow, Isnumber_Column).Address range_Is_IB = Cells(2, Is_IB_Column).Address &amp; ":" &amp; Cells(FinalRow, Is_IB_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False) range_Is_IB_fixed = Cells(2, Is_IB_Column).Address &amp; ":" &amp; Cells(FinalRow, Is_IB_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False) range_FolderId_cell = Cells(2, FolderId_column).Address(RowAbsolute:=False, ColumnAbsolute:=False) range_TradeId_cell = Cells(2, TradeId_column).Address(RowAbsolute:=False, ColumnAbsolute:=False) range_Exceptions = Cells(2, Exceptions_Column).Address &amp; ":" &amp; Cells(FinalRow, Exceptions_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False) Range(range_Isnumber).Formula = "=Isnumber(" &amp; range_TradeId_cell &amp; ")*1" Range(range_Is_IB).Formula = "=(RIGHT(" &amp; range_TradeId_cell &amp; ",2)= ""IB"")*1" Range(range_Exceptions).Formula = "=(SUMIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "," &amp; range_Isnumber_fixed &amp; ")= COUNTIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "))*1 +(SUMIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "," &amp; range_Is_IB_fixed &amp; ")= COUNTIF(" &amp; range_FolderId_fixed &amp; "," &amp; range_FolderId_cell &amp; "))*1 " Worksheets("Sheet1").UsedRange.AutoFilter Field:=7, Criteria1:="=1" </code></pre> <p>End Sub</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USAmatya
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel VBA deleting rows that have mixed values for a given index
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload