Matching values of an Array with the values of a range in MS Excel and store unmatched values into a separate Array? Topic is solved

[Sabestian Caine]

I have these values in an array A := {1:10, 2:60, 3:70, 4:50, 5:30}

And I have these values in range a1:a5 in excel-

05_05_21 @11_05_25.PNG (2.41 KiB) Viewed 576 times

I want to match values of array A with the values of range a1:a5 and for this I have written these codes so far which are quit incomplete, in fact I am not getting any idea how to do that-

Code: Select all

p:=1
A := {1:10, 2:60, 3:70, 4:50, 5:30}
Xl := ComObjActive("Excel.Application")

for key, value in a
	if (value = Xl.Range("a" p).value)
	MsgBox % key a_tab value
	p++

I want the values which are unmatched in range A (which are 80 and 90) should be stored into a separate array. You can see that only these two values 80 and 90 exist in range a1:a5 which do not exist in array A, so these two values should be stored into a separate array. PLEASE tell me how to do that? Thanks..

[boiler]

Code: Select all

A := {1:10, 2:60, 3:70, 4:50, 5:30}
Xl := ComObjActive("Excel.Application")
Unmatched := {}
for cell, v in Xl.Range("A1:A5") {
	found := 0
	for key, value in A {
		if (value = cell.text) { ; .text instead of .value to remove trailing zeros
			found := 1
			break
		}
	}
	if !found
		Unmatched[cell.address] := cell.text
}

for key, value in Unmatched
	output .= key . A_Tab . value "`n"
MsgBox, % output

[Chunjee]

https://biga-ahk.github.io/biga.ahk/#/?id=difference Creates an array of array values not included in the other given arrays. The order of result values are determined by the first array.

Code: Select all

A := new biga() ; requires https://www.npmjs.com/package/biga.ahk

values := {1:10, 2:60, 3:70, 4:50, 5:30}
Xl := ComObjActive("Excel.Application")
FileSelectFile, path
Xl.Workbooks.Open(path)
excelValues := []
for cell, value in Xl.Range("A1:A5") {
	excelValues.push(cell.text)
}
separateArray := A.difference(excelValues, values)
; => ["80", "90"]

[Sabestian Caine]

Code: Select all

A := {1:10, 2:60, 3:70, 4:50, 5:30}
Xl := ComObjActive("Excel.Application")
Unmatched := {}
for cell, v in Xl.Range("A1:A5") {
	found := 0
	for key, value in A {
		if (value = cell.text) { ; .text instead of .value to remove trailing zeros
			found := 1
			break
		}
	}
	if !found
		Unmatched[cell.address] := cell.text
}

for key, value in Unmatched
	output .= key . A_Tab . value "`n"
MsgBox, % output

Thanks dear boiler..

[Sabestian Caine]

Code: Select all

A := {1:10, 2:60, 3:70, 4:50, 5:30}
Xl := ComObjActive("Excel.Application")
Unmatched := {}
for cell, v in Xl.Range("A1:A5") {
	found := 0
	for key, value in A {
		if (value = cell.text) { ; .text instead of .value to remove trailing zeros
			found := 1
			break
		}
	}
	if !found
		Unmatched[cell.address] := cell.text
}

for key, value in Unmatched
	output .= key . A_Tab . value "`n"
MsgBox, % output

Sir, there is one problem in your codes-
suppose i have these values in array A := {1:80, 2:70, 3:50, 4:90, 5:30}
and these values in range a1:a5-

07_05_21 @10_38_14.PNG (2.27 KiB) Viewed 494 times

Now i run these codes-

Code: Select all

A := {1:80, 2:70, 3:50, 4:90, 5:30}
Xl := ComObjActive("Excel.Application")
Unmatched := {}
for cell, v in Xl.Range("A1:A5") 
{
	found := 0
	for key, value in A 
	{
		if (value = cell.text)
		{
			found := 1
			break
		}
	}
	if !found
	{
	Unmatched[cell.address] := cell.text
	}
}

for key, value in Unmatched
	output .= key . A_Tab . value "`n"
MsgBox, % output

Then it shows only one value in msgbox-

07_05_21 @10_41_03.PNG (7.6 KiB) Viewed 494 times

While it should show two values in msgbox which should be 60 and 50, as there are two 50s in range a1:a5. One 50 is at a1 and second is at a4 , so, while matching the values of range a1:a5 with the values of array, it should match only one 50 and it can be any one (whether the 50 which falls at a1 or a4) and it must show two values in msgbox one is 60 of-course and second is one 50 (which can be any one either of a4 or of a1). Please tell me how to solve this problem. Thanks sir...

[boiler]

Then remove each item from A when it's found so the same item is not found again:

Code: Select all

A := {1:80, 2:70, 3:50, 4:90, 5:30}
Xl := ComObjActive("Excel.Application")
Unmatched := {}
for cell, v in Xl.Range("A1:A5") {
	found := 0
	for key, value in A {
		if (value = cell.text) { ; .text instead of .value to remove trailing zeros
			found := 1
			A.Delete(key)
			break
		}
	}
	if !found
		Unmatched[cell.address] := cell.text
}

for key, value in Unmatched
	output .= key . A_Tab . value "`n"
MsgBox, % output

[Sabestian Caine]

Then remove each item from A when it's found so the same item is not found again:

Code: Select all

A := {1:80, 2:70, 3:50, 4:90, 5:30}
Xl := ComObjActive("Excel.Application")
Unmatched := {}
for cell, v in Xl.Range("A1:A5") {
	found := 0
	for key, value in A {
		if (value = cell.text) { ; .text instead of .value to remove trailing zeros
			found := 1
			A.Delete(key)
			break
		}
	}
	if !found

		Unmatched[cell.address] := cell.text
}

for key, value in Unmatched
	output .= key . A_Tab . value "`n"
MsgBox, % output

Thanks dear boiler...now these codes are perfect..

[boiler]

One other thing...If you want to leave array A intact instead of removing items from it, then just clone it to another array and use that for the comparison:

Code: Select all

A := {1:80, 2:70, 3:50, 4:90, 5:30}
B := A.Clone()
Xl := ComObjActive("Excel.Application")
Unmatched := {}
for cell, v in Xl.Range("A1:A5") {
	found := 0
	for key, value in B {
		if (value = cell.text) { ; .text instead of .value to remove trailing zeros
			found := 1
			B.Delete(key)
			break
		}
	}
	if !found
		Unmatched[cell.address] := cell.text
}

for key, value in Unmatched
	output .= key . A_Tab . value "`n"
MsgBox, % output