Formula for comparing two lists

[Nypsie98]

I have two lists that I want to compare. I have numbers in both lists, one in column C, the second in column H. I then have information in column I. I want to compare the list in column H to that in C. If a match is found between the two columns, I want excel to put the information in column I into column D. To complicate matters, there may be duplicate numbers within column H, but the data in the row's respective column I is different, but I want all of the information in both identical rows of column I to be in the single cell for the match of column C to go into column D. I have supplied an example because I know that sounds confusing. If it helps, the comma in Column D isn't needed just so the words are different (FYI, the words in column I and D are actually numbers as well, I just went with words so it isn't so confusing looking). Thanks, Ryan.

column C	Column D	E	F	G	Column H	Column I
100	Apple				100	Apple
200	Orange, Banana				200	Orange
250					200	Banana
400	Apple				300	Apple
500	Apple, Lemon				400	Apple
600					500	Apple
700	Apricot				500	Lemon
					700	Apricot
					800	Watermelon

<tbody>
</tbody>

 

[BenMiller]

Will column I ever have duplicate values for the same value in column H?

Will column H always be sorted ascending?

 

[Nypsie98]

Yes, it is possible that column H could have a duplicate value with the same or different value in column I.

Both column C and H could be sorted ascending.

Also the two "sides" can be thought of as two different tables (the sides on the right is a cut and paste from an other spreadsheet report).

Also, is there any way to do this outside of VBA? Its not a requirement, but my ability to work in VBA is very minimal.

 

[BenMiller]

If column H has 100 three times with column I having Apple, Apple, and Banana, respectively, what would you like to be returned?

 

[Nypsie98]

I would like to have column D show "Apple, Apple, Banana" where column C has 100. Basically, column D should have the aggregate of all column I where column H has 100.

 

[BenMiller]

I would be a lot easier to accomplish if the values can each be placed in their own column, instead of in one cell with a comma delimiting them. Any chance of this happening?

 

[Nypsie98]

That would be just fine. The spreadsheet can be altered as it is a tool for me to verify information from another list. In all the most number of repetions of the number would be four.

 

[BenMiller]

With your data arranged the way it is, put this in D1 and drag down and across:

=IF(COUNTIF($H$1:$H$9,$C1) < COLUMN(A1),"",INDEX($I$1:$I$9,MATCH($C1,$H$1:$H$9,0)+$C1+(COLUMN(A1)-1-$C1)))

(remove the spaces before and after the < )<column(a1),"",index($i$1:$i$9,match($c1,$h$1:$h$9,0)+$c1+(column(a1)-1-$c1)))< html=""></column(a1),"",index($i$1:$i$9,match($c1,$h$1:$h$9,0)+$c1+(column(a1)-1-$c1)))<>