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:

=INDEX($B$2:$B$10,INT((ROWS($1:1)-1)/ROWS($C$2:$C$13))+1)

and for list2 (G2) this

Formula:

=INDEX($C$2:$C$13,MOD((ROWS($1:1)-1),ROWS($C$2:$C$13))+1)

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:

=INDEX(hcol1,INT((ROWS($1:1)-1)/(ROWS(hcol2)*ROWS(hcol3)))+1)&" "&INDEX(hcol2,INT((ROWS($1:1)-1)/ROWS(hcol3))+1-(INT((ROWS($1:1)-1)/(ROWS(hcol2)*ROWS(hcol3))))*ROWS(hcol2))

and

Formula:

=INDEX(hcol1,INT((ROWS($1:1)-1)/(ROWS(hcol2)*ROWS(hcol3)))+1)&" "&INDEX(hcol3,MOD((ROWS($1:1)-1);ROWS(hcol3))+1)

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

Formula:

=IFERROR(INDEX(LEFT(list1,SEARCH(" ",list1)-1),MATCH(0,COUNTIF($I$1:I1,LEFT(list1,SEARCH(" ",list1)-1)),0)),"")

Formula:

=IFERROR(INDEX(RIGHT(list1,2),MATCH(0,COUNTIF($J$1:J1,RIGHT(list1,2)),0)),"")

Formula:

=IFERROR(INDEX(MID(list2,SEARCH(" ",list2)+1,LEN(list2)),MATCH(0,COUNTIF($K$1:K1,MID(list2,SEARCH(" ",list2)+1,LEN(list2))),0)),"")

where:
"list1" is defined with

Formula:

=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B$2:$B$100),1)

"list2" with

Formula:

=OFFSET(Sheet1!$C$2,,,COUNTA(Sheet1!$C$2:$C$100),1)

helper column 1 "hcol1" with

Formula:

=OFFSET(Sheet1!$I$2,,,COUNTIF(Sheet1!$I$2:!$I$50,"?*"),1)

"hcol2"

Formula:

=OFFSET(Sheet1!$J$2,,,COUNTIF(Sheet1!$J$2:!$J$50,"?*"),1)

"hcol3"

Formula:

=OFFSET(Sheet1!$K$2,,,COUNTIF(Sheet1!$K$2:!$K$50,"?*"),1)

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!