Macro to lookup data within multiple columns

MHamid · Feb 6, 2023

Hello,

Hopefully I will explain this correctly. I am looking for some assistance figuring out the best way to create a macro that will look at data in multiple columns and determine what is the account ownership.
I have possible ownership listed in columns AB-AI and also AR. Looking to create a code that will do the following.
If there is a Responsible Individual identified in column AR, then look in this column for business ownership and this will be the primary ownership ... the rest of the data in the remaining columns will be impacted businesses if different from Responsible Individual.
If there is no Responsible Executive, then look at the Business Name (Column AC), then look at Business Name2 (Column AD), then look at Business Name 6 (Column AG), then look at Business Name 7 (Column AH), then look at Business Name 8 (Column AI). Business Ownership should be listed within these columns, and I need the macro to look in that order. Majority of the time the data will be the same, but there will be instances where there might be additional businesses listed in the latter columns.
Anything in Business Name 3 and Business Name 4 (Columns AD and AE), then these will aways be listed in the impacted column (Column G).
If any of the columns (AR, AB, or AC) have "Business" in this column with an additional other business name, then the account is always "Not Owned" (Column BC) and "Shared" Column BD).
If any of the columns (AR, AB-AI) have "Business" and/or blanks in this column alone, then the account is always (Column BC) and "Business" (Column BD).
if any of the columns (AR, AB-AI) does not have "Business", then the account is an "owned" account (Column BC) and if there is a Responsible Individual identified in column AR, then look in this column for business ownership and this will be the primary ownership ... the rest of the data in the remaining columns will be impacted businesses if different from Responsible Individual.

In the Key sheet, I have some lookup values that I was thinking of using for this.
The goal is to weed through the data in the above-mentioned columns and provide "Final Results" in Columns (BC-BG).
Below is the best I could do to provide sample data.
Let me know if you need any further clarification.

Sample Data.xlsx

A

B

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

BB

BC

BD

BE

BG

BH

1

Final Results

2

Account Number

BL1

Business Name

Business Name 2

Business Name 3

Business Name 4

Business Name 5

Business Name 6

Business Name 7

Business Name 8

Area1

Area2

Area3

Area4

Area5

Area6

Area7

Area8

Responsible Individual

Ownership

Main Focus

Owned Area

Impacted

Area

3

S123456

GB

Business/Technology

Technology

ET

GB/ET

GB

ET

Not Owned

Shared

Technology

GB

4

S456789

ET

Business/Technology/Operations

GB/ET/IG

ICG

ET

ICG

Not Owned

Shared

Technology/Operations

GB

5

S789123

IG

Business

GB

IG

GB

Not Owned

Business

IG

6

S456123

PB

Operations

Technology

PB

Owned

Ops

Operations

PB

7

S852741

PB

Business

PB

Not Owned

Business

PB

8

S741963

ET

Operations/Technology

Operations/Other

PB/ET

PB

ET

Technology

Owned

Tech

Technology

Operations

ET

9

S741852

GB

Technology/Business/Operations

ET/Business

Technology

GB/ET

GB

Not Owned

Shared

Technology/Operations

GB

10

Main

Sample Data.xlsx

W

X

Y

Z

AA

AB

1

Ownership

Main Focus

Owned Area

Region

Impacted

Area

2

Owned

Ops

Operations

PB

3

Owned

Ops

Multi

PB

4

Owned

Tech

Technolgy

ET

5

Owned

Tech

Multi

ET

6

Not Owned

Business

IG

7

Not Owned

Shared

Operations

GB

8

Not Owned

Shared

Technolgy

GB

Key

Thank you,

MHamid · Feb 9, 2023

Hello,

I have the below Select Case code that bring over the Ownership. What I am trying to do next is create a code that will look at the results from the Select Case code results in column BC and then look at the values in columns (AR, AB-AI). First part I want to solve is if the data in columns (AR, AB-AI) contains only Business, then the result should be Business all else will be Shared. Would this work with Select Case or is For Each with some If Statements a better option?

This is the Case Code I am using to solve for Ownership.

VBA Code:

Option Explicit
Sub Ownership()
Dim sh As Worksheet
Dim lr As Long
Dim i&, rngD, rngB, rngC, B As String, C As String, D As String, Ownership()
Set sh = Sheets("Main")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
rngD = Range("AR3:AR" & lr).Value: rngB = Range("AB3:AB" & lr).Value: rngC = Range("AC3:AC" & lr).Value
ReDim Ownership(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        D = rngD(i, 1): B = rngB(i, 1): C = rngC(i, 1)
        Select Case True
            Case (D Like "*Non O&T Business*" Or D Like "Non O&T Business" Or D Like "Non O&T Business*" Or D Like "*Non O&T Business" Or D Like "") Or (B Like "*Non O&T Business*" Or B Like "Non O&T Business" Or B Like "Non O&T Business*" Or B Like "*Non O&T Business" Or B Like "") Or (C Like "Non O&T Business" Or C Like "*Non O&T Business*" Or C Like "Non O&T Business*" Or C Like "*Non O&T Business" Or C Like "")
                Ownership(i, 1) = "Non-O&T": GoTo z
            Case Else
                Ownership(i, 1) = "O&T Area": GoTo z
z:
        End Select
    Next
With Range("BC3").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = Ownership
End With
End Sub

This is the code I attempted to create for the Main Focus column (L1_Area in my code) for the "Not Owned" items (Non-O&T in my actual code).

VBA Code:

Sub L1_Area()
Dim sh As Worksheet
Dim lr As Long
Dim i&, rngD, rngB, rngC, rngE, B As String, C As String, D As String, E As String, L1_Area()
Set sh = Sheets("Main")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
rngD = Range("AR3:AR" & lr).Value: rngB = Range("AB3:AB" & lr).Value: rngC = Range("AC3:AC" & lr).Value: rngE = Range("BC3:BC" & lr).Value
ReDim Ownership(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        E = rngE(i, 1): D = rngD(i, 1): B = rngB(i, 1): C = rngC(i, 1)
        Select Case True
            Case (E = "Non-O&T")
                Case (D Like "Non O&T Business" Or D Like "") Or (B Like "Non O&T Business" Or B Like "") Or (C Like "Non O&T Business" Or C Like "")
                    L1_Area(i, 1) = "Business": GoTo z
                Case Else
                    L1_Area(i, 1) = "Shared": GoTo z
z:
        End Select
    Next
With Range("BD3").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = L1_Area
End With

I am getting the error "Subscript out of range" within the L1_Area(i, 1) = "Shared": GoTo z section of the code. 


End Sub

Thank you,

Alex Blakenburg · Feb 10, 2023

MHamid said:
I am getting the error "Subscript out of range" within the L1_Area(i, 1) = "Shared": GoTo z section of the code.

L1_Area is erroring out because you have neither populated it with a range nor have you dimensioned it using a Redim L1_Area(no of rows, no ol columns).

I can't follow you logic in your select case statements eg in Main Focus

MHamid said:
This is the code I attempted to create for the Main Focus column (L1_Area in my code) for the "Not Owned" items (Non-O&T in my actual code).

In Case D B C they are all OR which means if any one of those 3 is blank it will qualify for that Case. Is that really what you mean ?
You are using a Like without and wildcards "*" this is the same as "="

MHamid said:
This is the Case Code I am using to solve for Ownership.

D Like "*Non O&T Business*" Or will cover off D Like "Non O&T Business" Or D Like "Non O&T Business*" Or D Like "*Non O&T Business" making them superflous. Also here they are all OR so if even one of the column = blank ("") then that case option will be TRUE.

You refer to:

MHamid said:
"Not Owned" items (Non-O&T in my actual code

But this never appears in your sample in AB AC or AR and yet that is where your select Case is looking for it.

Can you give some clearer examples and explanation of when it should be Business and when is should be Shared.

MHamid · Feb 10, 2023

Hello,

Alex Blakenburg said:
L1_Area is erroring out because you have neither populated it with a range nor have you dimensioned it using a Redim L1_Area(no of rows, no ol columns).

Ah yes! I see what I missed. The ReDim I have in the second code should have been L1_Area and I left it as Ownership. I made the correction, but it's populating Business and Shared for anything found in column BC as "O&T Area", but I need it to populated for the Non-O&T ownership. What I'm looking to do here is if the cell value in column BC is "Non-O&T", then if "Non O&T Business" is the only text found within the cell in columns AR, AB, or AC, then the result should be "Business", if the cell value contains other along with "Non O&T Business", then the result should be "Shared".

Alex Blakenburg said:
In Case D B C they are all OR which means if any one of those 3 is blank it will qualify for that Case. Is that really what you mean ?
You are using a Like without and wildcards "*" this is the same as "="

I believe my last statement above should answer this question ... What I'm looking to do here is if the cell value in column BC is "Non-O&T", then if "Non O&T Business" is the only text found within the cells or if the cell value is blank in columns AR (Case D), AB (Case B), or AC (Case C), then the result should be "Business", if the cell value contains other text along with "Non O&T Business" (for example, Non O&T Business/Technology), then the result should be "Shared".
I thought that if I used wildcards here, then I'm saying "Non-O&T Business" and additional data, which I don't want. I just want to know id this text "Non-O&T Business" or blank is the only text listed (I have a cell where "Non-O&T Business" can be listed multiple times with "/" in between and I have another column where it's just "Non-O&T Business" value.

Alex Blakenburg said:
D Like "*Non O&T Business*" Or will cover off D Like "Non O&T Business" Or D Like "Non O&T Business*" Or D Like "*Non O&T Business" making them superflous. Also here they are all OR so if even one of the column = blank ("") then that case option will be TRUE.

Yes, in this part of the code, I need at least 1 one these to be true. If the additional Or statements are superfluous and the code still works without them, I can remove the extras and test to make sure it does what I need. Sometimes some cell values will be blank, which is why I thought Or would work.

Alex Blakenburg said:
But this never appears in your sample in AB AC or AR and yet that is where your select Case is looking for it.

Can you give some clearer examples and explanation of when it should be Business and when is should be Shared.

Sorry, my sample data is different than the actual data text because I can't share exactly what I have from work. So I just provided testing text, but didn't have time to enter the testing text within the code. I can provide an updated testing data, but that will take me a little bit of time.

Any other preliminary ideas before I provide an updated sample data?

Thank you,

Alex Blakenburg · Feb 11, 2023

See if this gives you any ideas.
Note: Your example
• has Row 7 as Business but it doesn't meet the criteria you outlined above
• has 1 row as Ops and 1 as Tech in Column BD - this is not covered in the information you have provided and your code clears BD before it puts in the new data so these codes will not appear.

VBA Code:

Sub L1_Area()
Dim sh As Worksheet
Dim lr As Long
Dim i&, rngD, rngB, rngC, rngE, L1_Area()
'Dim B As String, C As String, D As String, E As String              ' XXX Not used in this version of the code

Set sh = Sheets("Main")

With sh
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    rngD = .Range("AR3:AR" & lr).Value: rngB = .Range("AB3:AB" & lr).Value: rngC = .Range("AC3:AC" & lr).Value: rngE = .Range("BC3:BC" & lr).Value
End With

ReDim Ownership(1 To UBound(rngB), 1 To 1)
ReDim L1_Area(1 To UBound(rngB), 1 To 1)


Dim cntBusiness As Long
Dim cntBlank As Long
Dim cntCritCols As Long
cntCritCols = 3
' Logic for Business
' One of the 3 columns must be Business and the other columns can be blank or Business
For i = 1 To UBound(rngB)
    If rngE(i, 1) = "Non-O&T" Then
        cntBusiness = (rngB(i, 1) = "Business") + (rngC(i, 1) = "Business") + (rngD(i, 1) = "Business")
        cntBusiness = -cntBusiness                  ' Reverse sign VBA True = -1
        If cntBusiness > 0 Then
            cntBlank = (rngB(i, 1) = "") + (rngC(i, 1) = "") + (rngD(i, 1) = "")
            cntBlank = -cntBlank                  ' Reverse sign VBA True = -1
        End If
       
        If (cntBusiness + cntBlank) = cntCritCols Then
            L1_Area(i, 1) = "Business"
        Else
            L1_Area(i, 1) = "Shared"
        End If
    End If
Next i

With sh.Range("BD3").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = L1_Area
End With

End Sub

MHamid · Feb 16, 2023

Hi Alex,

I tested your code and I like it, but I'm looking to see if I need to tweak it for additional columns
I'm trying to rework my Ownership code, but I can't seem to figure out how I can make my logic work. This is the logic: If the data in Column "AR3:AR" & lr contains "Non-O&T Business", then column BC value needs to be "Non-O&T". Then if the data in Column "AB3:AB" & lr or the data in Column "AC3:AC" & lr contains "Non-O&T Business" or is blank, then column BC value needs to be "Non-O&T". Everything else should be for cells that do not contain "Non-O&T Business" or blanks in columns AB and AC, then column BC value needs to be "O&T Area".
What am I missing in my code to make this logic work?

VBA Code:

Sub Ownership()
Dim sh As Worksheet
Dim lr As Long
Dim i&, rngD, rngB, rngC, B As String, C As String, D As String, Ownership()
Set sh = Sheets("Main")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
rngD = Range("AR3:AR" & lr).Value: rngB = Range("AB3:AB" & lr).Value: rngC = Range("AC3:AC" & lr).Value
ReDim Ownership(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        D = rngD(i, 1): B = rngB(i, 1): C = rngC(i, 1)
        Select Case True
            Case D Like "*Non O&T Business*"
                Ownership(i, 1) = "Non-O&T": GoTo z
            Case (B Like "*Non O&T Business*" Or B Like "") Or (C Like "*Non O&T Business*" Or C Like "")
                Ownership(i, 1) = "Non-O&T": GoTo z
            Case Else
                Ownership(i, 1) = "O&T Area": GoTo z
z:
        End Select
    Next
With Range("BC3").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = Ownership
End With
End Sub

By the way what does this mean in the code you provided for me to test?

VBA Code:

cntCritCols = 3

Thank you,

Alex Blakenburg · Feb 17, 2023

I need to get clarification of the logic.
1) If AR contains "Non-O&T Business", do we still need to test AB & AC ?
2) If AB & AC are both blank, do you still want BC to be "Non-O&T" ?

MHamid · Feb 17, 2023

Alex Blakenburg said:
I need to get clarification of the logic.
1) If AR contains "Non-O&T Business", do we still need to test AB & AC ?
2) If AB & AC are both blank, do you still want BC to be "Non-O&T" ?

If column AR is true, then the value in BC is “Non-O&T”. If column AR is false, then the value in BC is “O&T Area”. However, if column AR is blank, then Test columns AB & AC. If there is a blank in both columns AB & AC, then BC will be “Non-O&T”.

Hope that makes better sense.

Alex Blakenburg · Feb 17, 2023

I would need new data from you for me to test this, but on a copy of your workbook give this a try:

VBA Code:

Sub Ownership()
Dim sh As Worksheet
Dim lr As Long
Dim i&, rngD, rngB, rngC, B As String, C As String, D As String, Ownership()
Set sh = Sheets("Main")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
rngD = Range("AR3:AR" & lr).Value: rngB = Range("AB3:AB" & lr).Value: rngC = Range("AC3:AC" & lr).Value
ReDim Ownership(1 To UBound(rngB), 1 To 1)
    For i = 1 To UBound(rngB)
        D = rngD(i, 1): B = rngB(i, 1): C = rngC(i, 1)
        Select Case True
            Case D Like "*Non O&T Business*"
                Ownership(i, 1) = "Non-O&T"
            Case Not (D Like "*Non O&T Business*")
                Ownership(i, 1) = "O&T Area"
            Case D = ""
            If B Like "*Non O&T Business*" Or C Like "*Non O&T Business*" Or _
                    (B = "" And C = "") Then
                        Ownership(i, 1) = "Non-O&T"
            Else
                Ownership(i, 1) = "O&T Area"
            End If
        End Select
    Next
With Range("BC3").Resize(UBound(rngB), 1)
    .ClearContents
    .Value = Ownership
End With
End Sub

MHamid · Mar 13, 2023

Hi Alex,

Thank you, I had to manipulate the code a bit to the exact verbiage for my data, but I was able to get it to work.
How can I manipulate this code to work for data that has multiple businesses. I'm looking to solve for a scenario where column BD shows "Shared" (based on sample data below), then column BG should be populated with data from columns AB-AI and AR excluding "Business" and duplicated data (looking for unique data). Would this be possible without helpers columns?

Sample Data.xlsx

A

B

AB

AC

AD

AE

AF

AG

AH

AI

AR

BB

BC

BD

BE

BG

BH

2

Account Number

BL1

Business Name

Business Name 2

Business Name 3

Business Name 4

Business Name 5

Business Name 6

Business Name 7

Business Name 8

Responsible Individual

Ownership

Main Focus

Owned Area

Impacted

Area

3

S123456

GB

Business/Technology

Technology

ET

Not Owned

Shared

Technology

GB

4

S456789

ET

Business/Technology/Operations

Business/Business/Technology/Operations

Business/Technology/Operations

Not Owned

Shared

Technology/Operations

GB

5

S789123

IG

Business

GB

Not Owned

Business

IG

6

S456123

PB

Operations

Technology

Owned

Ops

Operations

PB

7

S852741

PB

Business

Not Owned

Business

PB

8

S741963

ET

Operations/Technology

Operations/Other

Technology

Owned

Tech

Technology

Operations

ET

9

S741852

GB

Technology/Business/Operations

ET/Business

Technology

Not Owned

Shared

Technology/Operations

GB

Main

Let me know if you need more sample data.

Thank you,

Macro to lookup data within multiple columns

MHamid

Active Member

Excel Facts

MHamid

Active Member

Alex Blakenburg

MrExcel MVP

MHamid

Active Member

Alex Blakenburg

MrExcel MVP

MHamid

Active Member

Alex Blakenburg

MrExcel MVP

MHamid

Active Member

Alex Blakenburg

MrExcel MVP

MHamid

Active Member

Similar threads

Forum statistics

Share this page

Macro to lookup data within multiple columns

Active Member

Excel Facts

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock