Research problem in excel

[dudu3060]

Hi guys,

I am doing retrospective research in liver enzyme's elevated patients .

I am have two columns of ID's ( around 30,000-50,000) which i want to compare and see if there is a match.
I do want a partial match between the numbers in each column of 5 digits .
A is the premanent group.
C is a changable group of ID's that i want to compare each time to A.
I want that the matched number will be on B .

Is it possible?

How do i do that ?

Help please

---

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]11123400[/TD]
[TD]11123400[/TD]
[TD]111234[/TD]
[/TR]
[TR]
[TD]11189004[/TD]
[TD][/TD]
[TD]2234980[/TD]
[/TR]
[TR]
[TD]3339000[/TD]
[TD]3339000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]88890800[/TD]
[TD][/TD]
[TD]3339099[/TD]
[/TR]
</tbody>[/TABLE]

 

[J.Ty.]

Hi,

Welcome to MrExcel forum.

What do you mean exactly by a partial match of 5 digits?

I know that 3339000 matches 3339099 in your sense (the first 5 digits agree).
Does 3339000 match 9933390 in your sense (the last 5 digits agree)?
Does 3339000 match 3331100 in your sense (the first 3 and the last 2 digits agree)?
Does 3339000 match 3331010 in your sense (the first 3, the 5th digits and and the last digit agree)?
Does 3339000 match 0003339 in your sense (all digits agree, but the order is different)?


J.Ty.

 

[dudu3060]

Hi,

Welcome to MrExcel forum.

What do you mean exactly by a partial match of 5 digits?

I know that 3339000 matches 3339099 in your sense (the first 5 digits agree).
Does 3339000 match 9933390 in your sense (the last 5 digits agree)?
Does 3339000 match 3331100 in your sense (the first 3 and the last 2 digits agree)?
Does 3339000 match 3331010 in your sense (the first 3, the 5th digits and and the last digit agree)?
Does 3339000 match 0003339 in your sense (all digits agree, but the order is different)?

Hi j.try

The first five figures or the last five figures
Will help me. As long as there will be some connection
Between the numbers in the two columns.

Thank you very much


J.Ty.

Hi

The first five figures or the last five figures
Will help me. As long as there will be some connection
Between the numbers in the two columns.

Thank you very much

 

[J.Ty.]

Below is the solution. I have assumed that your response means
"(the first 5 digits in A agree with the first 5 digits in C) or (the last 5 digits in A agree with the last 5 digits in C)".
The formulas are rather slow to compute - please let me know if you want something faster.
I have assumed that I indicate a match by repeating the value from column A in column B.

Excel 2010
	A	B	C
1	11123400	11123400	111234
2	11189004		2234980
3	3339000	3339000
4	88890800		3339099
Sheet4

Cell Formulas
Range	Formula
B1	{=IF(SUM(--(LEFT(A1,5)=LEFT($C$1:$C$4,5)))+SUM(--(RIGHT(A1,5)=RIGHT($C$1:$C$4,5)))>0,A1,"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Good luck!

J.Ty.

BTW: You might also allow, e.g., "the first 5 digits in A agree with the last 5 digits in C", etc

 

[dudu3060]

Below is the solution. I have assumed that your response means
"(the first 5 digits in A agree with the first 5 digits in C) or (the last 5 digits in A agree with the last 5 digits in C)".
The formulas are rather slow to compute - please let me know if you want something faster.
I have assumed that I indicate a match by repeating the value from column A in column B.

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]11123400[/TD]
[TD="align: right"]11123400[/TD]
[TD="align: right"]111234[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]11189004[/TD]

[TD="align: right"]2234980[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3339000[/TD]
[TD="align: right"]3339000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]88890800[/TD]

[TD="align: right"]3339099[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]{=IF(SUM(--(LEFT(A1,5)=LEFT($C$1:$C$4,5)))+SUM(--(RIGHT(A1,5)=RIGHT($C$1:$C$4,5)))>0,A1,"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Good luck!

J.Ty.

BTW: You might also allow, e.g., "the first 5 digits in A agree with the last 5 digits in C", etc

THank you very much,

It seems like i cant really paste it with CTRL+SHIFT+ENTER

Any idea why?

 

[J.Ty.]

THank you very much,

It seems like i cant really paste it with CTRL+SHIFT+ENTER

Any idea why?

You should copy the formula without the { } braces into Excel, and, while still editing it, press CTRL+SHIFT+ENTER. This will produce the braces.

J.Ty.