return value to multiple columns based on specific text from a specific column

fullhouse · Nov 8th 2021

Hello,

I need help with creating a macro that will populate two other columns based on text criteria from column A. For example, if specific the text in column A matches "abc.com", then column B should be populated with "fakecompany1" and column C with "fakesegment1".

I tried to go with the code below to at least have column B populated, but the problem is that this way the value is locked in the cell and when cell value changes (which it with every new export) it will show "n/a" instead of identifying specific value in the entire column and when i tried to add "and" for the column C to be populated it was returning errors. So i need a code that will query whole column A for "abc.com" and then based on that will populate column B with "fakecompany1' and column C with "fakesegment1".

Code

Sub populateBnC()


If Worksheets("Sheet1").Cells(2, 1).Value = "abc.com" Then


Worksheets("Sheet2").Cells(2, 2).Value = "fakecompany1" And Worksheets("Sheet2").Cells(2, 3).Value = "fakesegment1"




Else


Worksheets("Sheet2").Cells(2, 2).Value = "n/a" And Worksheets("Sheet2").Cells(2, 3).Value = "n/a"
End If




End Sub

Display More

Thank you for your help

vcoolio · Nov 9th 2021

Hello Fullhouse,

You don't need the "AND" operator, so modifying your loop type code as follows may help:-

Code

Sub populateBnC()

Dim i As Long
Dim lr As Long: lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

        For i = 2 To lr
                If Sheet1.Cells(i, 1).Value = "abc.com" Then
                Sheet1.Cells(i, 2).Value = "fakecompany1"
                Sheet1.Cells(i, 3).Value = "fakesegment1"
                Else
                Sheet1.Cells(i, 2).Value = "n/a"
                Sheet1.Cells(i, 3).Value = "n/a"
                End If
        Next i

Application.ScreenUpdating = True

End Sub

Display More

You'll note that the last row has been defined so the code will loop each cell in Column A to the last row (the rows are defined by the variable 'i'), find the value 'abc.com' and add the text to Columns B and C. Based on the sample that you have supplied, Sheets("Sheet2") has the sheet code 'Sheet1' which I have used above.

In this line of your code:-

Code

If Worksheets("Sheet1").Cells(2, 1).Value = "abc.com" Then

I'm not sure if Worksheets("Sheet1") is a typo on your part.

A loop type code will work fairly quickly on a relatively small data set but if your data set is large, or could grow to be very large, using the AutoFilter will be a far better option. For example:-

Code

Sub Test()


Application.ScreenUpdating = False


With Sheet1.Range("A1", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))
.AutoFilter 1, "abc.com"
.Offset(1, 1).Resize(.Rows.Count - 1) = "Fakecompany1"
.Offset(1, 2).Resize(.Rows.Count - 1) = "Fakesegment1"
.AutoFilter
End With


Application.ScreenUpdating = True


End Sub

Display More

I hope that this helps.

Cheerio,

vcoolio.

fullhouse · Nov 9th 2021

Hello vcoolio,

Sheet1 was a typo ...

I had no clue that i do not need "and" there and was wondering why this was not working properly...

I will go with the autofilter option, because the data set is large and it is taking forever to run .. I had no clue that was even an option

I am new to vba so i truly appreciate your help and detailed explanation and suggestions. Thank you for your help with the macro and for saving a little bit of sanity that i have left

vcoolio · Nov 9th 2021

Hello Fullhouse,

You're welcome. I'm glad to have been able to assist.

Smart choice going with the Autofilter.

Does the Autofilter code in my first post do the task for you?

Cheerio,

vcoolio.

fullhouse · Nov 10th 2021

I finished the full code with the regular loop because I got issues with offset and until i figure that out, at least i will have something that will populate all the data.

Considering that in real scenario i have few hundred domains, i went with the range of column, instead of the cell as this is what i originally was asking for and finaly was able to find reference for it elsewhere . Imagine having 100k rows and at the row40k you have a domain ... so to avoid complications on the cell locking i was happy to find references to the column. Please keep in mind that the ranges are based on the real sheet with the column E as domainname, D as OpCo and C as Segment.

Code

If Range("E" & i).Value = ("abc.com") Then
Range("D" & i).Value = "fakecompany1"
Range("C" & i).Value = "faksegment1"

I am not sure how to do the offset on the full column range instead of locking it on the cell considering the amount of unique domains and their locations cell wise.

My understanding is that if abc.com is in the column E and i want fake company in the column D, then the column offset is -1, but I am not sure what do i put on for row? because abc.com will be inconsistent because with every new export, the only constant is the column for the values, and not cells.

I am sorry, i am at the very beginning of learning vba , so i may be a bit slow

vcoolio · Nov 10th 2021

Hello Fullhouse,

Quote

My understanding is that if abc.com is in the column E and i want fake company in the column D, then the column offset is -1, but I am not sure what do i put on for row?

For each instance of abc.com, the offset for the same row but the previous column(D) is written as:

.Offset(, -1)

However, based on your last post, it appears that the sample you supplied is not exactly what you are working with so the best option is for you to upload a sample of your workbook which is an exact replica of your actual working workbook. If your data is sensitive then please use dummy data. We'll only need a dozen or so rows of data to test with. Please also include the code that you are presently working with. This will make it much easier for us to resolve this for you and will spare us guessing at what is supposed to actually be happening.

Cheerio,

vcoolio.

fullhouse · Nov 10th 2021

I got it working!!!!!!

In the attached sheet, i posted screenshots of the criteria, desired results and the raw data table in the case someone else needs something similar to this.

Also the code is completed with the fake data sample. Thank you for the clarification on the offset part, that was pretty confusing when i was reading about it, so you clarifying it, helped tremendously,

I know that this looks very simple to you and other experienced folks, but I am almost dancing out of happiness as this is almost rocket science for me:) .. I guess there is still hope for me

Thank you for your guidance, patience and pointers. It helped a lot!!!!!

**royUK** · Nov 10th 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Nov 10th 2021

fullhouse make sure that you read the Forum Rules before posting again.

There is no nee for VB A to do this, you can easily use formulas - IF or IFS.

vcoolio · Nov 10th 2021

Hello Fullhouse,

You're welcome and I'm glad to have been able to assist.

I'm really happy that you spent the time foraging through information and working this out basically on your own. I laud you for that.

If you're interested, below is a condensed version of your code (but excluding the date part that you added in):-

Code

Option Explicit
Sub Format_Data2()
        Dim i As Long, lr As Long, ar As Variant
        Dim ws As Worksheet: Set ws = Sheet1
       
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("E1:E" & lr).AdvancedFilter 2, , ws.[M1], 1  'Unique values moved temporarily to Column M.
        ar = ws.Range("M2", ws.Range("M" & ws.Rows.Count).End(xlUp))
Application.ScreenUpdating = False
          For i = 1 To UBound(ar)
              With ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp))
                   .AutoFilter 1, ar(i, 1)
                        With .Offset(1)
                        If ar(i, 1) = "abc.com" Then
                                .Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany1"
                                .Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment1"
                        End If
                        If ar(i, 1) = "bcd.com" Then
                                .Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany2"
                                .Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment2"
                        End If
                        If ar(i, 1) = "edc.com" Then
                                .Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany3"
                                .Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment3"
                        End If
                        If ar(i, 1) = "blah.com" Then
                                .Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany2"
                                .Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment2"
                        End If
                        If ar(i, 1) = "bbb.com" Then
                                .Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany3"
                                .Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment3"
                        End If
                        If ar(i, 1) = "aa.com" Then
                                .Offset(, -1).Resize(.Rows.Count - 1) = "fakecompany2"
                                .Offset(, -2).Resize(.Rows.Count - 1) = "fakesegment2"
                        End If
                                .AutoFilter
                        End With
                End With
          Next i
ws.Columns.AutoFit
ws.Columns("M").Clear
Application.ScreenUpdating = True
End Sub

Display More

If you're willing to do a bit more research, what I've done in the above code is extract all the unique values in Column E using the AdvancedFilter and temporarily placing them in Column M. These unique values are then placed into an array(ar) which is then looped through and filtered for each unique value. This prevents many, many iterations which in turn speeds up the code and, as you stated earlier, you have around 100K rows of data so the saved iterations would be in the thousands. I tested this code on 150K rows and it took about three seconds to execute on my machine.

Column M is cleared at the end of code execution.

As you can see in the code, I've used 'IF' statements to identify the unique values for processing once filtered. You may want to research 'case statements in VBA' if you have the time. 'Case statements' can be used in place of 'IF' statements and may actually work more quickly.

Anyway, once again "well done" and good luck with your project.

I've attached your sample workbook with the above code implemented just so you can see how it works. There are about 230 rows of data in the sample.

Cheerio,

vcoolio.

Fullhouse.xlsm

fullhouse · Nov 10th 2021

Quote from royUK

fullhouse make sure that you read the Forum Rules before posting again.

There is no nee for VB A to do this, you can easily use formulas - IF or IFS.

Hello Roy,

Please accept my apologies for not adding the code tag. I will read through forum rules so this does not happen again..

fullhouse · Nov 10th 2021

Quote from vcoolio

If you're willing to do a bit more research, what I've done in the above code is extract all the unique values in Column E using the AdvancedFilter and temporarily placing them in Column M. These unique values are then placed into an array(ar) which is then looped through and filtered for each unique value. This prevents many, many iterations which in turn speeds up the code and, as you stated earlier, you have around 100K rows of data so the saved iterations would be in the thousands. I tested this code on 150K rows and it took about three seconds to execute on my machine.
Column M is cleared at the end of code execution.
As you can see in the code, I've used 'IF' statements to identify the unique values for processing once filtered. You may want to research 'case statements in VBA' if you have the time. 'Case statements' can be used in place of 'IF' statements and may actually work more quickly.
Anyway, once again "well done" and good luck with your project.

Thank you very much. I will be reading about case statements and also "ubound" tonight and then adjust the code accordingly.

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

return value to multiple columns based on specific text from a specific column

Participate now!

Share

Users Viewing This Thread