I am trying to build a massive keyword database for Google Ads, using Excel to substitute one keyword for another, which has already saved countless hours.
Because the data set I am working with will contain anywhere from 5-30 keyword sets that need to be recreated, the current structure I am using creates numerous blank cells between the keyword sets. I am trying to optimize the process further with a condensed list of data, ignoring all blank cells, so I can copy/paste all of the finished keywords into another spreadsheet.
The issue I am facing is only the first data entry in the list is being copied over.
keyword data replacement list
I have tried the following variations:
=IFERROR(INDEX($I$2:$I$20,SMALL(IF(TRIM($I$2:$I$20)<>"",ROW($I$2:$I$20)-ROW($I$2)+1),ROWS($I$2:$I$20))),"")
=IFERROR(INDEX($I$2:$I$20,SMALL(IF($I$2:$I$20<>"",ROW($I$2:$I$20)-1),ROW(A1))),"")
=IFERROR(INDEX($I$2:$I$20,SMALL(IF($I$2:$I$20<>"",ROW($I$2:$I$20)-ROW($I$2)+1,""),ROW(A2)-ROW($A$2)+1)),"")
However, as previously stated, only the first value is displayed in the list once the formula has been dragged down the column.
only first value displaying
I also tried another variation found here:
=IFERROR(INDEX($I$2:$I$20,SMALL(INDEX(($I$2:$I$20<>"")*ROW($I$2:$I$20),0),COUNTBLANK($I$2:$I$20)+ROW(I2))),"")
This gets me closer to what I am looking to do, showing more results, but it still includes the blank cells and is missing a few data entries.
data still missing
LILAust is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
It looks like your goal is to combine lists in D-F without blanks, and in column “DATA” you have already combined the values from D-F, but with blanks; the formula(s) tried were to remove blanks in “DATA”.
Assuming you have an older version of Excel ( SMALL was used in formulas in earlier version to remove blanks), here’s a formula, based on the ones you tried:
- select
J2:J20, with top one as the active cell (which usually the case) - enter the following in the formula bar
- press Control + Shift + Enter – there’s no need to fill down
- Here’s reference for array formulas: Create an array formula – Microsoft Support
=IF(
ROW(I2:I20) < ROW($I$2) + ROWS($I$2:$I$20) - COUNTBLANK($I$2:$I$20),
INDEX(
$I$2:$I$20,
SMALL(
IF(
($I$2:$I$20 <> ""),
($I$2:$I$20 <> "") * ROW($I$2:$I$20),
""
),
ROW(I2:I20) - 1
) - 1,
1
),
""
)
With Microsoft 365
It is very easy to combine (append) multiple columns into single column, while removing blanks, with TOCOL:
=TOCOL(D2:F8, 1, TRUE)
Please let me know if you need explanation for how the first formula works.
2
If you are on most recent versions of Excel then use FILTER() function. Try-
=FILTER($I$2:$I$17,ISNUMBER(SEARCH(D1,$I$2:$I$17)))
Put above formula to D2 cell and drag to right till F2.