Find multiple rows in excel file based off 2 criteria (colomns)

[Koekepeerke]

Hello everyone,

I want to use iLogic to find multiple rows from an excel spreadsheet. the spreadsheet contains 3 colomns and i want to try and get the value out of the 3rd colomn based on the first 2 colomns. The problem is that i have multiple rows with the same 2 values. using GoExcel.FindRow i can only find the first row with matching criteria. i want to find all the rows so i can then add the values of the third colomn to get a total so to speak. to get an idea of how such a spreadsheet might look like is here a small example:

in this case i would like to add the values 1 and 4 to get a total of this combination.

this functionality would be a part of a larger rule so i dont think its worth to copy it all in here it might only be confusing.

Thanks in advance for any help or pointers!

[Cadkunde.nl · Accepted solution]

Havent tested this code, but I think you need something like this:

Dim cellvalue As String = ""
Dim searchvalue As String = "LI0196"
Dim qty As Double = 0
For i = 1 To 20 'I search now in the first 20 rows, you could also count rows till you find a cell with value "" (nothing)
	cellvalue = GoExcel.CellValue("filename.xls", "Sheet1", "A" & i)
	
	If cellvalue = searchvalue Then
	qty = qty + GoExcel.CellValue("filename.xls", "Sheet1", "C" & i)
	End If
Next

msgbox(qty)

[Koekepeerke]

Hi @Cadkunde.nl ,

First of all thanks for the reply!

i already tried something like this myself but it didnt work because there are multiple rows that meet the statement. i will however definitely try your variation in a minute. this is something i tried myself: 

		GoExcel.Open(ExcelFile, "Blad1")
		Dim loopaantal As Double = 0
		
		For a As Integer = 0 To 20
		
			If GoExcel.CellValue(ExcelFile, "Blad1", "A" & a) = librarynummer And GoExcel.CellValue(ExcelFile, "Blad1", "B" & a) = coderingnummer Then
			
				loopaantal = loopaantal + GoExcel.CellValue(ExcelFile, "Blad1", "C" & a)
			
			End If
		Next
		
		MsgBox("loopaantal: " & loopaantal)

[Koekepeerke]

I just noticed your loop only searches on the first criteria but i need to search on both criteria (first 2 columns).

I will try to make a variation on yours that searches on both and ill let you know how it turned out.

[Cadkunde.nl]

Variatie met 'do until' en 2 criteria

Dim cellvalue As String = "temp"
Dim searchnummer As String = "LI0196"
Dim searchcodering As String = "22"

Dim qty As Double = 0
Dim i As Integer = 1

Do Until cellvalue = ""
	cellvalue = GoExcel.CellValue("filename.xls", "Sheet1", "A" & i)
	
	If cellvalue = searchnummer And GoExcel.CellValue("filename.xls", "Sheet1", "B" & i) = searchcodering Then
		qty = qty + GoExcel.CellValue("filename.xls", "Sheet1", "C" & i)
	End If
	i = i + 1
Loop


MsgBox(qty)

[Koekepeerke]

hey @Cadkunde.nl  the for loop did actually work just fine made it like this! Bedankt voor de hulp en de snelle reacties!

zo is ie geworden:

		Dim cellvalue As String = ""
		Dim searchvalue As String = librarynummer
		
		Dim cellvalue2 As String = ""
		Dim searchvalue2 As String = coderingnummer
		
		Dim qty As Double = 0 	
		
		Dim loopaantal As Double = 0
		
		For p = 1 To 20 
			
			cellvalue = GoExcel.CellValue(ExcelFile, "Blad1", "A" & p)
			cellvalue2 = GoExcel.CellValue(ExcelFile, "Blad1", "B" & p)
		
			If cellvalue = searchvalue And cellvalue2 = searchvalue2 Then
				
				loopaantal = loopaantal + GoExcel.CellValue(ExcelFile, "Blad1", "C" & p)
			
			End If
		Next
			
		MsgBox("loopaantal: " & loopaantal)