Posts by Twiceborn

Fluff13 good thought, though I had tried that earlier along with other combinations of spaces in front of and trailing the at, and it hits me with a #VALUE! error every time.

Good afternoon folks. Probably a simple formula, but I can't seem to make any combination I can think of work. Here's situation:

I have a column of records that have a combination of employee role and the area they work. I want to extract just their role into it's own column.

Every cell has the same layout of role "at" and then the location

The formula I have been working with to grab the role is:

=Trim(Left(G2,Search("at",G4)-1))

This works in most scenarios. The problem is that some of the roles have at within words such as Operations Manager, and so it will only grab "Oper".

I need some formula that is looking for the exact "at" word such as in "Operations Manager at Quality department" where it finds the preposition and returns only that ahead of the preposition.

Thanks in advance!

jolivanes this is fantastic. So dang close and already really neat. Two things: since "account" can actually have instances of "accounts" (with the 's') how would I account (pun intended) for that? I thought maybe adding a comma and "accounts" after "account" as another string, but that gave me an error. Then I thought maybe just repeating that code line but with "accounts", but that didn't work.

Second, deleting the row after a yellow highlight is not working but it really doesn't seem to make sense as to why (to me), since the inserting rows works fine and the only difference is the inclusion of the Offset(1) in the yellow related command (and if my basic knowledge is correct, that is Offset(1) is what is telling VBA to delete the row below).

You have already been a fantastic help, and I really appreciate it.

Hey jolivanes thanks for the continued interaction. I've uploaded a new version (v4) that I try to add more field identifiers without giving personal data and a bit more clarity. Let me know if that helps!

jolivanes thanks! I think this is on the right track, but I'm having issues with it. Does vbColor recongnize conditional formatting based highlights? I ask because it works fine on your sheet... it works fine when adding data to your sheet, but if I put conditional highlighting rules for changing the color of cells to red or yellow, the color related portions of your macro do nothing.

I checked the rgb value of a cell that has been conditionally formatted to red and it returns #FFFFFF. I was surprised to see that. Is that normal for conditional formatting to actually be white even though the visible color is something else?

I know how to assign a color to a cell using VBA generally, but not how to assign a color to a cell based on the text within the cell. So for example, every new record when pasted into my actual file, has "Select" and then the name of the Customer (all in the same line). So I want to highlight that cell that is the start of the new record (e.g., "Select Tom Smith") as vbRed. Does that make sense? What would that code look like?

I think that if I assign the highlights using VBA instead of conditional formatting, it should circumvent this issue I'm seeing.

Thanks in advance!

jolivanes thanks for that. I pasted the that second code directly into the test file I had uploaded. When I run the script, nothing appears to happen. No errors, but no rows inserted or deleted. So I played around a bit with it, and changed the ColumnIndex values to 1 (everywhere I saw a 3) based on my uneducated thought that a 3 would be referencing column C and not column A where the data is (is that a correct understanding of ColumnIndex values?). When I ran it after that change, it did delete the rows that had the text in them, but still did not insert/delete rows related to the color.

Thoughts?

jolivanes yes, that is an accurate understanding of the request. Really sorry if I left out critical information in the initial OP:

All records have the red highlighted field, not all records have the yellow highlighted field. Those records that don't have the yellow field end up having less "rows" when copy/pasted. So for the purposes of this exercise, think of the Yellow field as # of Accounts. Some of our records have multiple accounts with us and some have none. If they have none, the site doesn't say 0, it just leaves that field completely blank.

I need to have the same number of rows per record so that I can more easily manipulate the data with some formulas I've been working up. I've uploaded a new version that gives the name to the yellow highlights.

It isn't technically ending up with the same as the beginning because not all records have the yellow field. Does that help clarify? Thanks again, truly, for engaging with me!

royUK I agree. It would be way better to use the text, but sadly the inconsistency of the data prohibits that

Hey jolivanes, I've attached a mock file below that shows what I'm hoping to accomplish. I hope I made it clear in the attached example what I'm trying to accomplish. It may sound convoluted, but I think most of that is because of the inconsistency of the data I'm working with.

Good evening. I'm sure this is child's play for most of you, but I'm trying to simplify a horrible data entry task. I have a wealth of customer data that is not in a consistent format as it is copy/pasted for an html based source (some records are missing entries and thus throws off the number of "lines" each record has when pasted into Excel).

I am trying to get a consistent number of blank lines for each record, as some of my housekeeping formulas are built around spaces, and the best way I THOUGHT to do that would be to insert a line above each red highlighted cell, and then remove a line underneath each yellow highlighted cell. Color coding certain cells was housekeeping for me to find inconsistencies in records.

The first two row deletion loops based on text works fine. The code for inserting a row above the red highlighted cells is not, and I haven't yet tried my hand at deleting rows after a yellow highlight. Thanks in advance for any help.

Here's what I so far:

Sub DeleteRows()
    Dim rng As Range
    Dim pos As Integer
    Dim LastRow As Long
    
    
    Set rng = ActiveSheet.UsedRange
    
    For i = rng.Cells.Count To 1 Step -1
        pos = InStr(LCase(rng.Item(i).Value), LCase("Show me"))
        If pos > 0 Then
            rng.Item(i).EntireRow.Delete
        End If
    Next i

For i = rng.Cells.Count To 1 Step -1
        pos = InStr(LCase(rng.Item(i).Value), LCase("Extra Level"))
        If pos > 0 Then
            rng.Item(i).EntireRow.Delete
        End If
    Next i
    

For i = rng.Cells.Count To 1 Step -1
    If Cells.Interior.ColorIndex = 3 Then
        Rows(rng.Cells(i, 1).Row).Insert shift:=xlDown

    End If
Next i

End Sub