Combine list data from two worksheets depending on criteria, into a list on a third worksheet

[vicomte777]

Hi MrExcel experts

there has been many an occasion I have turned to forums such as these for answers, yet have found answers without need of registering. This is the first time I've been unable to work out a solution and thus must plead for your kindness.

I have three worksheets (excuse the below, I'm making it up off the top of my head for illustration purposes only):

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Title 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Category A[/TD]
[TD]Category B[/TD]
[TD]Category C[/TD]
[TD]Category D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]GM[/TD]
[TD]Car[/TD]
[TD]Like[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]Ford[/TD]
[TD]Car[/TD]
[TD]Dislike[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]Boeing[/TD]
[TD]Plane[/TD]
[TD]Like[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4[/TD]
[TD]Subaru[/TD]
[TD]Car[/TD]
[TD]Dislike[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5[/TD]
[TD]Airbus[/TD]
[TD]Plane[/TD]
[TD]Dislike[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD]Daihatsu[/TD]
[TD]Car[/TD]
[TD]Like[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Title 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Category A[/TD]
[TD]Category B[/TD]
[TD]Category C[/TD]
[TD]Category D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7[/TD]
[TD]Apples[/TD]
[TD]Fruit[/TD]
[TD]Like[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]Watermelon[/TD]
[TD]Fruit[/TD]
[TD]Dislike[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9[/TD]
[TD]Cauliflower[/TD]
[TD]Vegetable[/TD]
[TD]Like[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10[/TD]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[TD]Dislike[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to make a list/table on Sheet3 which lists the corresponding Category A number to only those items in the above two worksheets, which are Like'd.

I've attempted nesting Match calls within Index formulas and various vlookup options, however as I'm searching through a column on the right for 'like's and then trying to find their corresponding reference numbers to the left, vlookup doesn't give me much luck.

Notes:
The lists on Sheet1 and Sheet2 are not of uniform length,so was attempting to wrap IFERROR formula around the INDEX(MATCH) combo so that when it finished looking through one worksheet, it would error and go to the Sheet2.
I'm trying to get the subset of column A from both sheets that correspond to values of 'Like' to appear, one after the other.
Once I have these values appearing properly, the Category B and Category C colums can fairly easily be filled through Vlookup formula.

I'm trying to come towards a table which looks like this:

Sheet3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Title 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Category A[/TD]
[TD]Category B[/TD]
[TD]Category C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]GM[/TD]
[TD]Car[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]Boeing[/TD]
[TD]Plane[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]Daihatsu[/TD]
[TD]Car[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]Apples[/TD]
[TD]Fruit[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]9[/TD]
[TD]Cauliflower[/TD]
[TD]Vegetable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope I explained myself well enough...

 

[Aladin Akyurek]

Category A seems to consist of a sequence of non-repeating integers. Is this a chance event or really a feature of your data?

 

[vicomte777]

Thank you for your response and interest Aladin.

A bit of both.

Each row of data in the actual product has currently got a sequential number next to it, however this is open to being changed by my boss or his boss.

It may be that Sheet1 may find its rows having some unique identifier such as:
A1
A2
A3
A4

and Sheet2

B1
B2
B3
B4

and so on, in which case they won't be sequential and of the integer variety. As these references/unique identifiers are potentially open to change, I didn't want to rely on them in any ROW/S() stuff.

(As an Australian and to a resident of the Netherlands, I also apologize for the unconscious use of airoplanes in my document above. It is very much all over our media, as I'm sure it is for you)

 

[vicomte777]

I've got one list adding onto the end of the other, but I need to use Match I guess to make it only list the entries which have corresponding 'Like's within the row. I think my head's gone to wool trying to work this one out so I suspect my ability to code the correct formula to enable it being dragged down may be quite affected.

=IF(ISERROR(INDEX(Sheet1!$A$3:$A$14, ROWS($A$2:A4))), IF(ISERROR(INDEX(Sheet2!$A$3:$A$12, ROWS($A$2:A4)-ROWS(Sheet1!$A$3:$A$14))), "", INDEX(Sheet2!$A$3:$A$12, ROWS($A$2:A4)-ROWS(Sheet1!$A$3:$A$14))), INDEX(Sheet1!$A$3:$A$14, ROWS($A$3:A5)))

 

[Aladin Akyurek]

Thank you for your response and interest Aladin.

A bit of both.

Each row of data in the actual product has currently got a sequential number next to it, however this is open to being changed by my boss or his boss.

It may be that Sheet1 may find its rows having some unique identifier such as:
A1
A2
A3
A4

and Sheet2

B1
B2
B3
B4

and so on, in which case they won't be sequential and of the integer variety. As these references/unique identifiers are potentially open to change, I didn't want to rely on them in any ROW/S() stuff.

(As an Australian and to a resident of the Netherlands, I also apologize for the unconscious use of airoplanes in my document above. It is very much all over our media, as I'm sure it is for you)

Ok, they do not form necessarily a sequence, but they are non-repeating, I presume.

Sheet1, A1:D8, and Sheet2, A1:D6, house the relevant data. The headers are in row 2 on each sheet.

The processing occurs in Sheet3, A:C...

[TABLE="width: 278"]
<TBODY>[TR]
[TD="class: xl65, width: 123, bgcolor: white"]5
[/TD]
[TD="class: xl66, width: 119, bgcolor: white"]Title 3
[/TD]
[TD="class: xl65, width: 129, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 123, bgcolor: white"]Category A
[/TD]
[TD="class: xl66, width: 119, bgcolor: white"]Category B
[/TD]
[TD="class: xl66, width: 129, bgcolor: white"]Category C
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]1
[/TD]
[TD="class: xl65, width: 119, bgcolor: white"]GM
[/TD]
[TD="class: xl65, width: 129, bgcolor: white"]Car
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]3
[/TD]
[TD="class: xl65, width: 119, bgcolor: white"]Boeing
[/TD]
[TD="class: xl65, width: 129, bgcolor: white"]Plane
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]6
[/TD]
[TD="class: xl65, width: 119, bgcolor: white"]Daihatsu
[/TD]
[TD="class: xl65, width: 129, bgcolor: white"]Car
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]7
[/TD]
[TD="class: xl65, width: 119, bgcolor: white"]Apples
[/TD]
[TD="class: xl65, width: 129, bgcolor: white"]Fruit
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]9
[/TD]
[TD="class: xl65, width: 119, bgcolor: white"]Cauliflower
[/TD]
[TD="class: xl65, width: 129, bgcolor: white"]Vegetable
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"][/TD]
[TD="class: xl65, width: 119, bgcolor: white"][/TD]
[TD="class: xl65, width: 129, bgcolor: white"][/TD]
[/TR]
</TBODY>[/TABLE]

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Define the following names using Formulas | Name Manager...

CatA as referring to:

=arrayunion(Sheet1!$A$3:$A$8,Sheet2!$A$3:$A$6)

CatD as referring to:

=arrayunion(Sheet1!$D$3:$D$8,Sheet2!$D$3:$D$6)

and Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(CatA)))

A1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(CatA<>"",IF(CatD="Like",
  MATCH("~"&CatA,CatA&"",0))),Ivec),1))

A3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)<=$A$1,
  INDEX(CatA,SMALL(IF(FREQUENCY(IF(CatA<>"",IF(CatD="Like",
  MATCH("~"&CatA,CatA&"",0))),Ivec),Ivec),ROWS($A$3:A3))),"")

B3, just enter and copy down:

=IF($A3="","",LOOKUP(REPT("z",255),CHOOSE({1,2},
  VLOOKUP($A3,Sheet1!$A$3:$B$8,2,0),
  VLOOKUP($A3,Sheet2!$A$2:$B$6,2,0))))

C3, just enter and copy down:

=IF($A3="","",LOOKUP(REPT("z",255),CHOOSE({1,2},
  VLOOKUP($A3,Sheet1!$A$3:$C$8,3,0),
  VLOOKUP($A3,Sheet2!$A$2:$C$6,3,0))))

See the workbook that implements the foregoing...
https://dl.dropboxusercontent.com/u/65698317/vicomte777 conditional sublist.xlsm

 

[vicomte777]

Hi
I'm getting 'End If without block If'
Code is as per your post. I can't view your file/macro at work.

 

[vicomte777]

Hey, I think I got it working! Thank you

 

[Aladin Akyurek]

Hey, I think I got it working! Thank you

You are welcome.