Need help referencing duplicated values and matching for 4000+ cells

Brass Contributor

Good Afternoon All,

I am trying to reference a column and match data that is found within that same column to a different column.

 

Please see the attached workbook and I am working in sheet "SMS Conus Travel Source".

 

I need to replace all the "NO GEO LOC FOUND" values that are fill colored white with their assigned "GEOLOC - Filter" value that is found in column H. I conditionally formatted all duplicate values based on column F "ICAO - Filter", now I just need a formula that can match the "GEOLOC - Filter" value based on its matched "ICAO - Filter" value. 

 

Thanks!

7 Replies

@marshalltj67 

replace all the "NO GEO LOC FOUND" values that are fill colored white with their assigned "GEOLOC - Filter" 

 

Can you show your expected result according to the raw data?

Additionlly,there are many sheets in the workbook.

Are these worksheets all relative to your question?

 

I guess more guys may understand what you need to accomplish if you delete some unrelative sheets and upload mini sample workbook.

@peiyezhu 

Please see the attached the mini sample workbook - my apologies for not doing this firsthand!


All the white filled "NO GEO LOC FOUND" cells have an assigned value that is linked to a cell with a matching "ICAO - Filter" value which is highlighted red as it is a duplicated value. I need a formula that assigns the GEOLOC value to that matched value which will get rid of all the white filled "NO GEO LOC FOUND" values.

 

Thanks!

 

Marshall

@marshalltj67 

Thanks for your response.

I am still some unclear.

 

All the white filled "NO GEO LOC FOUND"

Do you want to identify the by the background color even they have same value?

Range("B3") background color is white.

Range("B43") background color is like orange.

 

both ranges have same value NO GEO LOC FOUND.

 

Why only need replace Range("B3") rather than both ranges?

 

 

an assigned value that is linked to a cell with a matching "ICAO - Filter" value which is highlighted red as it is a duplicated value. 

 

I do not understand above words you mentioned "value which is highlighted red as it is a duplicated value. ".

Do these relative to the question?

 

I only found only Sheet1 as raw datas.

Can you provide your expected result according to the raw datas?

So that I can understand what you really mean.

 

@peiyezhu 

 

Please see the attached updated workbook that shows the expected result of the formula. 

 

Thanks!

 

Marshall

=FILTER(A3:B4254,B3:B4254<>B3)

=XLOOKUP(A3,D2:D4000,E2:E4000)

 

@marshalltj67 

 

 

Thank you!

Unfortunately, I do not have access to the FILTER and XLOOPUP functions as I am using older version of excel and have restricted access for updating it.

Is there anyway I can accomplish the filter with VLOOKUP?

Marshall

@marshalltj67 

Sorry I am not familiar with Excel formula.

if with sql,may like below:

//select * from Sheet1 limit 20; cli_no_header; select iif(f02 like 'NO GEO LOC FOUND',(select group_concat(distinct(f02)) from Sheet1 b where b.f02 not like 'NO GEO LOC FOUND' and b.f01 like a.f01 ),f02) f02 from Sheet1 a;

 

Screenshot_2023-12-07-09-42-30-746_cn.uujian.browser.jpg

https://m.bilibili.com/video/BV1494y177Mh?buvid=XX85E838539534AD650323D79CEE8DA100517&from_spmid=mai...