Partial Match Column A which has 14 Digits (match first 8 digits) to Column B (which have 8 digits)

yousafalvi

New Member
Joined
May 26, 2021
Messages
2
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hello,

I have an excel sheet where Column A contains less cells then Column B i.e Column A has 100 cells and Column B has 1500 Cells
and Column A has 14 digits data and B has 8 digits Data
i want to partial match that if Column A data is present in any cell of Column B (only first 8 digits) it highlights or give matched result in Column C
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi and welcome to MrExcel.
Try this:

varios 26may2021.xlsm
ABCD
1Data 14 digitsData 8 digitsExists in AExists in B
2123456789012345575296501
3906873868775093401876600
4549340284014325923647600
5499672484641701234567810
6869851236576167685226801
7935479654941869774115700
8311118205629786117838800
9948759453140538698512311
10829085981049596240731201
11813419730
12217218370
13829085981
14656982370
15299250190
16232968680
17516478100
18372851220
19948759451
20295224000
Hoja7
Cell Formulas
RangeFormula
D2:D10D2=COUNTIF(B:B,LEFT(A2,8)+0)
C2:C20C2=SUMPRODUCT((LEFT($A$2:$A$100,8)=B2&"")*1)
 
Upvote 0
well it works fine also, but is there any more detailed way, because after this query i have to count the number of cells in Column A with the search result SUM to match all the 100 columns are found with the sum of search results, what if one cells f Column A8 first digits doesnt matched with Column B 8 digits , how to figure out that one cell
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top