Finding matching cells then returning data of another cell

[GenericPat]

Hey guys,

Here's my dilema this morning... I have roughly 10 000 data lines and I need to perform a search that will match columns A and B to identical cells in A and B and then return the datas from C beside each of these identical A and B's in another cell (Cell D)

Example:

Cell A: 654
Cell B: 3
Cell C: 123

I want to find another row in which cells A and B are (654 and 3 respectively) and then take the value of cell C and combine it with cell C above (123)

Any help?

Pat.

[FDibbins]

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

[GenericPat]

Here's a sample

Sample.xlsx

[e.isayev]

The formula above concatenates only 2 of them. If there are more than 2 then it will take first 2.
=INDEX($C$2:$C$6000,MATCH(1,(A2=$A$2:$A$6000)*(B2=$B$2:$B$6000),0))&CHAR(10)&INDEX($C$2:$C$6000,2) CSE

[GenericPat]

Many times I have alot more then two

Any one else can help?

[GenericPat]

And your formula always returns "9" which is not what I was looking for

[dredwolf]

try this in d1
=IF(AND(A2=A3,B2=B3),C2&CHAR(10)&C3,IF(AND(A2=A1,B2=B1),C1&CHAR(10)&C2,""))

select d1, double click the crosshairs (or drag down the entire list ...brrr )

[dredwolf]

sorry missed the more than 2 match requirement...will try further experiments

[GenericPat]

Doesn't seem to be working... :S

Sample.xlsx

Check it out...

Thanks bud,

Pat.

[e.isayev]

=index(if((a4=$a$2:$a$6000)*(b4=$b$2:$b$6000)=1,$c$2:$c$6000,""),1)&char(10)&iferror(index(if((a4=$a$2:$a$6000)*(b4=$b$2:$b$6000)=1,$c$2:$c$6000,""),2),"")&char(10)&iferror(index(if((a4=$a$2:$a$6000)*(b4=$b$2:$b$6000)=1,$c$2:$c$6000,""),3),"")&char(10)&iferror(index(if((a4=$a$2:$a$6000)*(b4=$b$2:$b$6000)=1,$c$2:$c$6000,""),4),"")&char(10)&iferror(index(if((a4=$a$2:$a$6000)*(b4=$b$2:$b$6000)=1,$c$2:$c$6000,""),5),"")

cse

[GenericPat]

Doesn't work... Can you send the workbook so I can see that it works on yours D:

[dredwolf]

not sure why the formula comes up #name in your sheet, worked fine (except for the not matching more than2...) on the sample I downloaded..

[GenericPat]

Darn...

[e.isayev]

Because its an array formula it may slow your pc.

[dredwolf]

@eisayew...no kidding !!! I had to close the workbook so I could do ANYTHING else

[GenericPat]

Lol seems to be working but why does it only work for the first few lines?

[e.isayev]

@eisayew...no kidding !!! I had to close the workbook so I could do ANYTHING else

ok I will try it with sumproduct function

[e.isayev]

I uploaded file but entered formula for the first 15 rows.
=TRIM(INDEX(LARGE(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0),ROW(INDIRECT("1:"&SUM(--(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0)>0))))),1)&" "&IFERROR(INDEX(LARGE(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0),ROW(INDIRECT("1:"&SUM(--(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0)>0))))),2),"")&" "&IFERROR(INDEX(LARGE(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0),ROW(INDIRECT("1:"&SUM(--(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0)>0))))),3),"")&" "&IFERROR(INDEX(LARGE(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0),ROW(INDIRECT("1:"&SUM(--(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0)>0))))),4),"")&" "&IFERROR(INDEX(LARGE(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0),ROW(INDIRECT("1:"&SUM(--(IF((A2=$A$2:$A$15)*(B2=$B$2:$B$15)=1,$C$2:$C$15,0)>0))))),5),""))

Later you may enlarge the function from 15 to whatever number you want

[FDibbins]

ok this isnt pretty, but ig gives you want you want...

=IF(COUNTIF(E:E,E2)=1,E2, IF(E2=E1,H1, IF(E2=E3,C2&" "&C3& IF(E3=E4," "&C4& IF(E4=E5," "&C5& IF(E5=E6," "&C6,""),""),""),"")))

However, it will be necessary for you to sort the data, because in some instances, the "match" is not on the next row down

[dredwolf]

came up with this

=IF(AND(A2=A1,B2=B1),IF(D1<>"",D1&CHAR(10)&C2,C1&CHAR(10)&C2),"")

however, the final combination will only show in the last consecutive matching cell..