excel - Create named range with cells where value matches? -

excel - Create named range with cells where value matches? -

i have table 2 columns:

employee company 970423-4829 vete 970212-2398 alfalaval 970212-2398 delaval 970423-4829 verktyg 970423-4829 verktyg 960822-7587 arla 970423-4829 test3 961225-7590 test 970911-1287 kamel 970911-1287 kanel

i want create named range contains company lines employee.

if "employee" 970212-2398, want range be

alfalaval delaval

if "employee" 970911-1287, want range be

kamel kanel

is possible using excel and/or vba?

there seems dissenting comments suggest improve off solution looked ideal sub scripting.dictionary object , work them here go.

sub create_employee_named_ranges() dim n long, r long, vemp variant dim demps new scripting.dictionary demps.comparemode = textcompare activesheet r = 2 .cells(rows.count, 1).end(xlup).row if not demps.exists(.cells(r, 1).value) demps.add key:=.cells(r, 1).value, _ item:=chr(39) & .name & chr(39) & chr(33) & .cells(r, 2).address else demps.item(.cells(r, 1).value) = _ demps.item(.cells(r, 1).value) & chr(44) & chr(39) & .name & chr(39) & chr(33) & .cells(r, 2).address end if next r end activeworkbook n = 1 .names.count if left(.names(n).name, 4) = "enr_" _ .names(n).delete next n each vemp in demps .names.add name:="enr_" & replace(vemp, chr(45), chr(95)), _ refersto:=chr(61) & demps.item(vemp) next vemp end demps.removeall: set demps = nil end sub

you have go vbe's tools, references , add together microsoft scripting runtime list. note not utilize actual employee identifier dashes illegal characters in name of named range (probably due utilize in subtraction) replaced them underscores.

excel vba excel-vba