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.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]column C
[/TD]
[TD]Column D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]Column H
[/TD]
[TD]Column I
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]Apple
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]200
[/TD]
[TD]Orange, Banana
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200
[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]250
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200
[/TD]
[TD]Banana
[/TD]
[/TR]
[TR]
[TD]400
[/TD]
[TD]Apple
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]500
[/TD]
[TD]Apple, Lemon
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]400
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]600
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]700
[/TD]
[TD]Apricot
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500
[/TD]
[TD]Lemon
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]700
[/TD]
[TD]Apricot
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]800
[/TD]
[TD]Watermelon
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)))<>