CODEWITHSUNDEEP
excelvba
Zed A.
Zed A.

Reputation: 33

excel VBA select a value from a list (in another sheet) based on a condition

In sheet "Selection" I input a Gender (M or F) in column A, and a corresponding value in column B.

enter image description here

In sheet "Sizes", I have lists of the available sizes for each gender, like this.

enter image description here

In sheet "Selection", I want it to write on Column C the corresponding size (that must be always be higher than column B). If there is not an available size, it must write "Not available!"

enter image description here

Upvotes: 0

Views: 669

Answers (2)

Mrig
Mrig

Reputation: 11702

Enter the following formula in Cell C2 of Selection sheet

=IFERROR(IF(A2="M",INDEX(Sizes!$A$3:$A$10,MATCH(TRUE,Sizes!$A$3:$A$10>B2,0)),INDEX(Sizes!$B$3:$B$10,MATCH(TRUE,Sizes!$B$3:$B$10>B2,0))),"Not Available")

This is an array formula so commit it by pressing Ctrl+Shift+Enter. Drag/Copy down as required.

See image for reference

enter image description here

EDIT :

Following is VBA solution:

Sub Demo()
    With Application
        .ScreenUpdating = False             'stop screen flickering
        .Calculation = xlCalculationManual  'prevent calculation while execution
    End With

    Dim selectionSht As Worksheet, sizeSht As Worksheet
    Dim selectionLR As Long, sizeLR As Long, lastColumn As Long
    Dim dict As Object
    Dim rng As Range, cel As Range, genderRng As Range, valueRng As Range
    Dim key As Variant
    Dim colName As String

    Set dict = CreateObject("Scripting.Dictionary")
    Set selectionSht = ThisWorkbook.Sheets("Selection") 'Selection sheet
    Set sizeSht = ThisWorkbook.Sheets("Sizes")  'Sizes sheet
    selectionLR = selectionSht.Cells(selectionSht.Rows.Count, "A").End(xlUp).Row    'last row in Selection sheet
    sizeLR = sizeSht.Cells(sizeSht.Rows.Count, "A").End(xlUp).Row   'last row in Sizes sheet
    lastColumn = sizeSht.Cells(2, sizeSht.Columns.Count).End(xlToLeft).Column   'last column in Sizes sheet using row 2
    Set valueRng = selectionSht.Range("B2:B" & selectionLR) 'data with value in Selection sheet

    'storing all genders and corresponding column number from Sizes sheet in a dictionary
    With sizeSht
        Set rng = .Range(.Cells(2, 1), .Cells(2, lastColumn))
        For Each cel In rng
            dict.Add cel.Value, cel.Column
        Next cel
    End With

    With selectionSht
        For Each cel In .Range(.Cells(2, 3), .Cells(selectionLR, 3)) '3 is column no for results to be displayed
            colName = Replace(.Cells(1, dict(CStr(cel.Offset(0, -2)))).Address(True, False), "$1", "")  'get column name from column 2
            Set genderRng = sizeSht.Range(colName & "3:" & colName & sizeLR)    'set column for index/match formula
            cel.FormulaArray = "=IFERROR(INDEX(Sizes!" & genderRng.Address & ",MATCH(TRUE,Sizes!" & genderRng.Address & ">" & cel.Offset(0, -1) & ",0)),""Not Available"")"
            cel.Value = cel.Value
        Next cel
    End With

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Let me know if anything is not clear.

Upvotes: 2

A.S.H
A.S.H

Reputation: 29332

You can use this (normal) formula in C2 and fill down:

C2:
=IFERROR(AGGREGATE(15,6,Sizes!$A$3:$B$10
  /(Sizes!$A$2:$B$2=A2)/(Sizes!$A$3:$B$10>=B2),1), "Not Available!")

enter image description here

Upvotes: 1

Related Questions