CODEWITHSUNDEEP
excelgoogle-sheets
BenjaminK
BenjaminK

Reputation: 783

Find all duplicates in a column and return all of the same with an offset to left in the same cell

I want to search column I in "sheet_02" for duplicated cells and if some are found I want to return for every cell match the cell -7 columns left. All of the same duplicates with the same matching cell value in one cell and for the next matches the next cell below.

To clarify here my example:

Source: "sheet_02"

<cell B1> name_01    <cell I1> item_01
<cell B2> name_02    <cell I2> item_02
<cell B3> name_03    <cell I3> item_01
<cell B4> name_04    <cell I4> Item_03
<cell B5> name_05    <cell I5> item_01
<cell B6> name_06    <cell I6> item_03

The result I'm looking for: "sheet_01"

<cell A1> name_01
          name_03
          name_05

<cell A2> name_02

<cell A3> name_04
          name_06

An example with hard drives of the source: to clarify in my sheet the values representing "item_01" in my question are random strings but some with the same name. And the same for "names_01". So in my sheets where I want to use that code, it looks like my example below. Also, my list of values is much longer.

Hopefully, this example of hard drives makes it clearer. This is still the same pattern.

Source: "sheet_02"

<cell B1> "Datalibary (30.0GB)"           <cell I1>  "Seagate Barracuda"
<cell B2> "Pictures (10.5GB)"             <cell I2> "Western Digital"
<cell B3> "Movies (100.30GB)"             <cell I3> "Seagate Barracuda"
<cell B4> "Textdocuments (04.GB)"         <cell I4> "Lacie"
<cell B5> "Software (230.10GB)"           <cell I5> "Seagate Barracuda"
<cell B6> "Notes"                         <cell I6> "Lacie"

But I'm struggling.

I know I need to use something like that. But that's only the beginning. I'm stuck by trying to return the duplicates with an offset of -7. VLOOKUP won't work as it only returns one value. And I need all duplicates to be grouped and the offset on top of it.

=join(char(10), sort(sheet_02!I1:I6))

Upvotes: 1

Views: 74

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

For Excel: I turned your data into a Table so I could use Structured References. But you could use regular references if you need to.

Formula:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(myTbl[Column1], myTbl[Column8]= INDEX(UNIQUE(myTbl[Column8]),ROWS($1:1)))),"")

and fill down until you start to see blanks:

Results with your original data:

enter image description here

With your second set of data:

enter image description here

Note

For Google Sheets, the equivalent formula, as translated from Excel to Sheets by Google, could be:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Sheet_02!$B$1:$B$6, Sheet_02!$I$1:$I$6= INDEX(UNIQUE(Sheet_02!$I$1:$I$6),ROWS($1:1)))),"")), 1, 1)

But this seems to work just as well:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Sheet_02!$B$1:$B$6, Sheet_02!$I$1:$I$6= INDEX(UNIQUE(Sheet_02!$I$1:$I$6),ROWS($1:1)))),"")

Upvotes: 2

zummon
zummon

Reputation: 986

For Google Sheets

Let put more FILTER() in sort() and use Column B to get the unique value

"B1" put =UNIQUE(sheet_02!I$1:I$16)

"A1:..." put =join(char(10), sort(FILTER(sheet_02!B$1:B$16,sheet_02!I$1:I$16=B1)))

I hope it'll work for you,

Upvotes: 0

Related Questions