Combining two lists in two equal lists

Hello,

I'm looking to find a solution in which there is a method to have uneven two lists and have become two lists with matching values. I attempted to use an ISNUMBER Match function but it wouldn't go past one value...I enclosed an sample of this problem in this post...Anytime dedicated to finding a solution would be appreciated...


Thank You

Hi,
for list1 (F2) use this

Formula:

Please Login or Register  to view this content.

and for list2 (G2) this

Formula:

Please Login or Register  to view this content.

Hi TudyBTH

Thanks for taking time to look into this...I tried the formula and I have a question is there a way to have List 1 match up with only similar values to Match 2 I enclosed another example in this post

Hello @Azumi,

Thanks for taking a gander at this I enter this formula and List1 it didn't match with List2 it seemed to offset List1 by a few rows...I provided a sample of this within this post..

Thanks again

You can just add IF function to do that for list 2

=IF(D2="","",IF(ROWS(E$2:E2)>3*ROWS($C$2:$C$5),IF(ROWS(E$2:E2)-14>2*ROWS($C$6:$C$9),INDEX($C$10:$C$13,MOD(ROWS(E$2:E2)-13,ROWS($C$10:$C$13))+1),INDEX($C$6:$C$9,MOD(ROWS(E$2:E2)-1,ROWS($C$6:$C$9))+1)),INDEX($C$2:$C$5,MOD(ROWS(E$2:E2)-1,ROWS($C$2:$C$5))+1)))

with three helper columns and five define names, formulas will look like:

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

for helper columns use array formulas (with Ctrl+Shift+Enter):

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

where:
"list1" is defined with

Formula:

Please Login or Register  to view this content.

"list2" with

Formula:

Please Login or Register  to view this content.

helper column 1 "hcol1" with

Formula:

Please Login or Register  to view this content.

"hcol2"

Formula:

Please Login or Register  to view this content.

"hcol3"

Formula:

Please Login or Register  to view this content.

see attached

Hi TudyBTH

Thanks for the idea but I seem to be still encountering a problem with that formula you suggested it worked for a limited amount of data but the more data was added the more it began to produce odd results. I suppose the true purpose of this problem is to seek if there is a relative method to have two lists in which list 1(groups) match against the relevant words in list 2(words)....I have a VERY large list and was wondering if there could be a solution for this. I provided an example within this post.

Hi,
In this case I recommend using VBA.

@TudyBTH

I was thinking along the same lines..any recommendations about how to go about this is welcomed...

Ok,
First check the attached file if it does what you want.
I assumed that input data is in columns B,D,F,H,I.
The lists are updated when changes occur in the data input.

see attached

@TudyBTH

This is BRILLIANT!!!! This works perfectly..this is exactly the result I was looking to achieve TudyBTH..Thank you for helping finding a solution to this problem!!

Thanks again!