What Formula to use? Find cells w/ specific word and copy-paste word in other cell

[Andrew Kedem]

I'm preparing a spreadsheet populated with multiple invoices and multiple line items. I can't import with blank cells in "Item" column.

I'm looking for a formula needed to find cells with specific word," Totals ", (i.e. "#Totals#") and populating other cell in same row with word, "Total".

For all cells in Column A with " Totals " in cell, I want to have "Total" entered in same row, Column M, "Item" column.

In every row where the word "Totals" appears, with a space before and after the word, I need Column M to populate with the word, Total.

What formula and/or macro can I use to accomplish this? See sample file.

 

[GraH - Guido]

Hi Andrew,

I'm bit puzzled by your query. There is no real advantage to making a formula in a single cell for each empty cell of a column. (Awkwardly explained, but you can't drag down/across.) Since those cells are empty why not just type it in? Go To -> Special -> Empty cells -> Type "Total" and press CTRL + ENTER to populate them. You could filter the data with contains "Total" first.

Formula might be something like
=IF(ISNUMBER(SEARCH("total",A106)),"Total","")

 

[Andrew Kedem]

Hi Guido.

I'm sorry for my puzzling query.

I'll be working with spreadsheets consistently with about 500 lines. I'll be importing the data into 30-50 invoices in QuickBooks.

I have Header rows and Subtotal and Total rows. Currently, the only data in these rows is the header name, item subtotal name or the item Total name.

Instead of a formula, should I be making a macro for this?
I want to look up empty cells in the columns I mentioned before (I, L & M) in rows in which "Totals" appears in column A. Then I want "Total" to appear in the blank cells in Column M.

If I were to use your formula, where would I paste it?

 

[GraH - Guido]

Hi again Andrew,

I have applied on your workbook what I explained previously.
In the light of automation, a macro could indeed be a better solution. Not to be provided by me though.

I do have question regarding the process. Perhaps the "template" used, could be made as such the word "Total" is already present where you'd like it?
Don't know QuickBooks...

 

[Fluff13]

With code, (including the code from your other thread)

Sub AndrewKedem()
  Dim Lr As Long
 
  Lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Total"",""""))", "@", .Address))
  End With
  With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Total"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
  End With
  With Range("E2:E" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
  With Range("L2:M" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
End Sub

 

[Andrew Kedem]

Thank you Guido for all your time and generous advice.
The macro from Fluff13 did the trick. Thanks Fluff13!
Have a great day.
Andrew

 

[Fluff13]

You're welcome & thanks for the feedback