Macro to COUNTIF in a consolidated file for rows meeting certain criteria

**Tshulha** · 05-19-2023, 02:32 PM

Hi. I have multiple files with a key field each.
- The key field can be located in any column - the column is not known in advance.
- All key fields per file are listed in a consolidated file from the left to the right in a table such as File 1, File 2 File 3, File 4, File 5, File 6. CHECK fields are comparing if the values in 2 fields to the left match. The number of files - 6 is an example. It can be larger (up to 50).
- Hyperlink to each file is provided in the consolidated file.
- I have to count how many lines are in the File1, File2, File3, File4, File5, File6 in the certain field (field name is provided in line 4 for each file in the Consolidated file) match Criteria ID. This could be ID1, ID2, IDs. Criteria for each file is also provided (criteria ID is provided in line 5 for each file in the Consolidated file).

I have to loop through all the files to count number of IDs in a given field meeting given criteria and populate COUNTIF values to the fields File1, File2, File3, File4, File5, File6 in the consolidated file. This should be done with VBA instead of current formulas provided in File1, File2, File3, File4, File5, File6 fields.

Please see consolidated file and sample files File1, File2, File3, File4, File5, File6 attached. The file should be unzipped to C:\ directory to enable hyperlinks.

Thank you.

**jindon** · 05-20-2023, 12:24 AM

This doesn't use CountIf function, so no need to open each files.

Please Login or Register  to view this content.

**Tshulha** · 05-20-2023, 01:22 AM

jindon, this is awesome! It works! Thank you

**Tshulha** · 05-20-2023, 02:41 AM

hi jindon. Thank you for the code. How should the code be edited if files are split into 2 subfolders? For ex, File 1, File 3, File 5 are located in C:\COUNT VBA\Extract, while File 2, File 4, File 6 are located in C:\COUNT VBA\Load. We can move from the consolidated file's directory to 2 subfolders: Extract and Load. Thank you

**jindon** · 05-20-2023, 02:59 AM

If the numbers at the end of the file name always one digit then

Please Login or Register  to view this content.

**Tshulha** · 05-20-2023, 03:30 AM

Originally Posted by jindon

If the numbers at the end of the file name always one digit then

Please Login or Register  to view this content.

Thank you, jindon. The name of the file could be
be any and is indicated in line 3. Examples of the file names are BioInfo, EmploymentInfo, CompInfo, Emails, Phones, PaymentDetails, etc.

**jindon** · 05-20-2023, 03:41 AM

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**Tshulha** · 05-20-2023, 10:16 AM

jindon, thank you. It looks like it does not work. Please see attached how the files are organized and named. The number of pairs of files can be bigger.

**jindon** · 05-20-2023, 10:32 AM

You have changed the data layouts.

Is this the last change?
If not, need to see exact layouts.

**Tshulha** · 05-20-2023, 11:17 AM

Originally Posted by jindon

You have changed the data layouts.

Is this the last change?
If not, need to see exact layouts.

I will send the last layout soon with the maximum possible number of columns

**Tshulha** · 05-20-2023, 08:09 PM

Originally Posted by jindon

You have changed the data layouts.

Is this the last change?
If not, need to see exact layouts.

jindon, please see final layout attached.

**jindon** · 05-20-2023, 09:54 PM

Now the code finds the file location from the Link assigned by Hyperlink formula.
If the link is invalid, that column will be left blank.

You need to be very careful that row 4, A4:EI4 to be exact, should be kept complete blank.
Getting correct data range is the one of the most important factors in coding.

Please Login or Register  to view this content.

Edit
Code has been cleaned:

**Tshulha** · 05-20-2023, 11:01 PM

jindon, thank you. Now everything works great except lines 9 and 10: in line 9 formulas are overwritten by blank cells and CHECK formula, in line 10 headers are now Column1, Column2, 0, Column3, Column4, 02, Column5, Column6, 03, etc. Is it something that can be fixed by starting validation from line 11 instead of 9?

**jindon** · 05-20-2023, 11:14 PM

OOps, missed to change one line, 5 to 7.

Code in #12 has been fixed.

**Tshulha** · 05-20-2023, 11:29 PM

thank you, jindon. Now the code works for Extract and Load fields in lines 9 and 10, but the issue still persists for CHECK columns in lines 9 and 10. Would it be possible to fix this?

**jindon** · 05-21-2023, 12:06 AM

One more same change, see #12.

**Tshulha** · 05-21-2023, 01:03 AM

thank you. Almost great except the code adds 2 more lines at the bottom with blank IDs and formulas generated by the code. If this is hard to fix, we can leave as it is and I will be manually deleting the lines each time the code is run, but if this is an easy fix, it would be great to know how the lines can be removed Attachment 830017
Also, I noticed that the formula returns 1 even if there are multiple records for a certain ID. For ex, it is expected to return 3 if there are 3 lines for the selected ID in the file. Can this be fixed?

**jindon** · 05-21-2023, 01:47 AM

Can not replicate it, so upload the workbook that you have such problem.

**Tshulha** · 05-21-2023, 11:31 AM

please see attached. The code is saved in ThisWorkbook. I copied-pasted #12 and the problem with 2 extra lines is gone.

Only formula IF requires replacement with COUNTIF: for ex, in Extract_Phones users A1, A2, A3, A4, A5, A6, A7, A8 have 5 records each - expectation to see 5,5,5,5,5,5,5,5 in the consolidated file in the correspondent column. Also, in Load_14. Phones users C2, C3, C4, C5, C6, C7, C8, C9 have 5 records each - expectation to see 5,5,5,5,5,5,5,5 in the consolidated file in the correspondent column.

For ex, for Load_14. Phones: the code should search for the column named "personInfo.person-id-external" in the "Load_14. Phones" file, count the number of entries for each user from column "ID3" in the Consolidated file and place this count to the field "Load_14. Phones" in the Consolidated file.
Capture1.PNG

I changed the formula to this one:
.FormulaR1C1 = "=if(isnumber(match(rc" & c(0) & "," & fn & "c" & c(1) & ":c" & c(1) & ",0)),countif(" & fn & "c" & c(1) & ":c" & c(1) & "," & "rc" & c(0) & "),0)"

This formula works, but Consolidated file returns values only when the target files are open, otherwise the cells return #VALUE! since Excel cannot calculate values from the closed files. What about opening each file in the code, populating formulas, evaluating values for the entire column, copying and pasting values over the formulas and then closing the file for each column in the range S:EH?

**jindon** · 05-21-2023, 09:13 PM

If we are testing the codes with different files, we never come to the end.
All your files uploaded before the last one have no duplicates, so I used Match function.
change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**Tshulha** · 05-21-2023, 09:34 PM

this one works perfect! thank you, jindon!

Just the last question: if I have more than 1000 records, let's say, 10000, should I replace 1000 by 10000 in the code?

**jindon** · 05-21-2023, 09:38 PM

Correct.
Sumproduct function with full column reference will take time, so better apply possible maximum row to limit.

**Tshulha** · 05-21-2023, 09:45 PM

thank you, jindon. Your solution is very clean and elegant. I like it very much!

**jindon** · 05-21-2023, 09:56 PM

Glad you like it.

Also, you may not be aware that you can thank by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation.

**Tshulha** · 05-21-2023, 11:18 PM

sure, jindon. Done.

**Tshulha** · 05-25-2023, 11:30 AM

hi jindon. I was wondering if it is easy to make the change in this code to skip the column if the hyperlink is not valid instead of populating NULL?

Sub extract_vs_load()
Dim fn As String, s As String, c(1), i As Long, ii As Long, iii As Long
Application.ScreenUpdating = False
With Sheets("dashboard").[a5].CurrentRegion
For ii = 19 To .Columns.Count Step 3
For iii = 0 To 1
.Cells(7, ii + iii).Resize(.Rows.Count - 6).ClearContents
If .Cells(2, ii + iii).Formula Like "=HYPERLINK(*" Then
s = Replace(Split(Split(Mid$(.Cells(2, ii + iii).Formula, 2), ",")(0), "(")(1), """", "")
If Dir(s) <> "" Then
fn = "'" & Left$(s, InStrRev(s, "")) & "[" & Mid$(s, InStrRev(s, "") + 1) & "]Sheet1'!"
c(0) = Application.Match(.Cells(4, ii + iii), .Rows(6), 0)
c(1) = ExecuteExcel4Macro("match(""" & .Cells(3, ii + iii) & """," & fn & "r1:r1,0)")
With .Cells(7, ii + iii).Resize(.Rows.Count - 6)
If (IsNumeric(c(0))) * (IsNumeric(c(1))) Then

.FormulaR1C1 = "=sumproduct((" & fn & "r2c" & c(1) & _
":r10000c" & c(1) & "=rc" & c(0) & ")+0)"
End If
End With
End If
End If
Next
.Cells(7, ii + iii).Resize(.Rows.Count - 6).FormulaR1C1 = "=rc[-2]=rc[-1]"
Next
End With
Application.ScreenUpdating = True
End Sub

Thank you!

**jindon** · 05-25-2023, 08:56 PM

Just delete one line in red

Please Login or Register  to view this content.

**Tshulha** · 05-25-2023, 09:15 PM

great! it worked. thank you

Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Re: Macro to COUNTIF in a consolidated file for rows meeting certain criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sum of products meeting criteria on multiple rows matching other criteria w/o helper col?

[SOLVED] Count Data Meeting Criteria Across Columns Meeting Criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions