Formula: Test if array is exact match to another array.

[Lewzerrrr]

Hi,

I need to in whole do 2 tests but unsure why it's not working.

I'm running a simple copy page down copy.. macro on another programme that copies the data and then pastes into Excel, however with said macro it will duplicate the copy once at the bottom of the list.

This will create a 7 row, 10 column paste of data * however much until I stop running the macro.

I will paste this in B4 and the data can range from B4:K(end of data).

What I need to test is if in column B there's a Y which is simple but then also test if a 7, 10 matches the 7, 10 below e.g does B12:K18 = B19:K25. If either of these tests come true then mark a Y.

So something like {=IF(B12="Y","Y",IF(B12:K18=B19:K25,"Y",""))} but this brings back a Y even if B12 doesn't have a Y and even if the whole of B12:K18 doesn't equal B19:K25.

What's the best way to go about this?

 

[NdNoviceHlp]

Write a macro that doesn't duplicate the copy. Dave

 

[Lewzerrrr]

Write a macro that doesn't duplicate the copy. Dave

Would be good however the programme is AS400 and unsure how technical with code it can be. Not many articles around AS400 language. It’s pretty much just [COPY]
[PAGE-DOWN]
[COPY-NEXT]

 

[NdNoviceHlp]

Does it have to be a formula solution or is VBA OK? I don't really understand the conditions that U outlined to describe what and where U are looking for duplicates? What happens when U do find the duplicates or do U want to manually clear them? Dave

 

[Lewzerrrr]

Does it have to be a formula solution or is VBA OK? I don't really understand the conditions that U outlined to describe what and where U are looking for duplicates? What happens when U do find the duplicates or do U want to manually clear them? Dave

VBA can work, I already have a code that can clear contents if Header 1 has a Y in it.

ws.Columns("B").Replace "Y", "#N/A", xlWhole, , False, , False, False

On Error Resume Next


Intersect(ws.Columns("B:K"), ws.Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow).ClearContents


On Error GoTo 0

For example 1, check to see if header 1 has a Y and then clear contents of all data below this.
For example 2, check to see if the 2 X 2 box is a duplicate of the 2 X 2 box above, if so clear contents of all data below this.

What I was trying to do was in a separate column, make a formula that will test both conditions and return a Y and then for my macro to clear contents if a Y was first found.

See below examples of small data sets, you can see where the macro copies a certain section and duplicates if I don't turn it off. (2 X 2 box)

AS400 Programme 1

Example 1

Result 1

AS400 Programme 2

Example 2

Result 2

Header 1

Header 2

Header 1

Header 2

Header 1

Header 2

Header 1

Header 2

Header 1

Header 2

Header 1

Header 2

Apple

Apple

Apple

Apple

Apple

Apple

Pear

Pear

Pear

Pear

Pear

Pear

Orange

Orange

Orange

Orange

Orange

Orange

Grape

Grape

Grape

Grape

Grape

Grape

Lime

Lime

Lime

Lime

Lime

Lime

Y

Lemon

Y

Lemon

Lemon

Lemon

Lemon

Y

Watermelon

Y

Watermelon

Watermelon

Watermelon

Watermelon

Y

Strawberry

Y

Strawberry

Strawberry

Strawberry

Strawberry

Y

Watermelon

Watermelon

Y

Strawberry

Strawberry

Y

Watermelon

Watermelon

Y

Strawberry

Strawberry

Y

Watermelon

Watermelon

Y

Strawberry

Strawberry

Y

Watermelon

Watermelon

Y

Strawberry

Strawberry

Y

Watermelon

Y

Strawberry

<tbody>
</tbody>

 

[Lewzerrrr]

***********
SOLVED
***********

Managed to use this formula and then used a VBA code that searches for a "Y" in column A and clear contents across and below.

=IF(OR(B3="Y",C3=" ",AND(EXACT(B3:C4,B1:C2))),"Y","") confirm with CTRL + SHIFT + ENTER.

Data applied to:

	Example 1
	Header 1	Header 2
		Apple
		Pear
		Orange
		Grape
Y
Y	Y	Lemon
Y	Y	Watermelon
Y	Y	Strawberry
Y	Y	Watermelon
Y	Y	Strawberry
Y	Y	Watermelon
Y	Y	Strawberry
Y	Y	Watermelon
Y	Y	Strawberry
Y	Y	Watermelon
Y	Y	Strawberry
Y	Y	Watermelon
Y	Y	Strawberry

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[NdNoviceHlp]

Glad U figured it out. Thanks for posting your outcome. Dave