Lookup from the duplicate data and skip correspond blank cells.
Since Vlookup have some limitation that suppose if we do have lots of repeated data then lookup functions used first cum data and listed correspond cell data . In this blog, I have lots of repeated "email" data and I need to list correspond "address". And, "address" column consists many blanks cells.
Example: In A2 i.e email column : sid@xyz.com and correspond D2 i.e Address column : NIL (Blank) , whereas A17 : sid@xyz.com and D17 : 7 street
If we used VLookup or Index match function the result would be blank as it comes first, but we need to skip blank cells and use non blank cell i.e D17 : 7 street.
To rectify this problem, I used CONCATENATE function, where I concatenate email and address and the cells which consists only email are ignored.
COLUMN A : Duplicate Email data
COLUMN D : Address data where some address cells are blank.
Steps:
First , I created unique emails data .
Formula :
COLUMN F: Unique Email Data
{=IFERROR(INDEX(combined_email,SMALL(IF(MATCH(combined_email,combined_email,0)=ROW(INDIRECT("1:"&ROWS(combined_email))),ROW(INDIRECT("1:"&ROWS(combined_email))),""),ROW(INDIRECT("1:"&ROWS(combined_email))))),"")}
For above formula explanation please visit below link:
where , combined_email : A:A
COLUMN H: Concatenate Column A and Column D and if it consists only email then ignore it (i.e blank).
=IF(CONCATENATE(A2,D2)=A2,"",CONCATENATE(A2,D2))
COLUMN I: Column H left part which is email.
=LEFT(H2,LEN(H2)-LEN(D2))
COLUMN J: Column H right part which is address.
=IFERROR(RIGHT(H2,LEN(H2)-LEN(A2)),"")
COLUMN K: For non blank Address, use index and match function and new column ie. I:J would be now lookup_array
=IFERROR(INDEX($I$2:$J$53,MATCH(F2,$I$2:$I$53,FALSE),2),"-")
And to count repeated times that particular email appears use below formula
Column G:
=COUNTIF(combined_email,F2)
Thank you !
