i have column containing departments , column containing appt, can, no show. want calculate how many time every department has occurred appt, can , no show. code using extracts unique values of department , using if statements counts value of appt, can , no show.
data set: http://bit.ly/1hkvaxr code unique departments:
public sub getting_unique_departments() dim x dim objdict object dim lngrow long if len("e") > 0 , len("y") > 0 set objdict = createobject("scripting.dictionary") x = application.transpose(range("e" & 2, cells(rows.count, "e").end(xlup))) lngrow = 1 ubound(x, 1) objdict(x(lngrow)) = 1 next range("y" & 2 & ":" & "y" & objdict.count + 1) = application.transpose(objdict.keys) end if end sub code check appt, can, walk , no show each department.
sub calculation() nname0 = "department" nname1 = "appt" nname2 = "walk" nname3 = "can" nname4 = "no show" cells(1, 25).value = nname0 cells(1, 26).value = nname1 cells(1, 27).value = nname2 cells(1, 28).value = nname3 cells(1, 29).value = nname4 dept_row_number = 2 dept_lastrow 'dept_lastrow finds last row of unique department listed in y col , sheet_lastrow finds last row of input data sheet. ncount1 = 0 ncount1 = 0 ncount2 = 0 ncount3 = 0 ncount4 = 0 row_number = 1 search_string1 = activesheet.cells(dept_row_number, 25) doevents row_number = row_number + 1 item_in_review1 = activesheet.cells(row_number, 5).value item_in_review2 = activesheet.cells(row_number, 3).value if instr(item_in_review1, search_string1) > 0 , instr(item_in_review2, "appt") > 0 ncount1 = ncount1 + 1 elseif instr(item_in_review1, search_string1) > 0 , instr(item_in_review2, "walk") > 0 ncount2 = ncount2 + 1 elseif instr(item_in_review1, search_string1) > 0 , instr(item_in_review2, "can") > 0 ncount3 = ncount3 + 1 elseif instr(item_in_review1, search_string1) > 0 , instr(item_in_review2, "no show") > 0 ncount4 = ncount4 + 1 end if loop until row_number = sheet_lastrow cells(dept_row_number, 26).value = ncount1 cells(dept_row_number, 27).value = ncount2 cells(dept_row_number, 28).value = ncount3 cells(dept_row_number, 29).value = ncount4 next is there easy way because in case have more 1 column, code cucumbersome.
byron wall correct pivot tables natural choice -- can streamline vba. aware of dictionaries exploiting them more. recommend using binding -- in tools/references add reference microsoft scripting runtime , can write code along following lines. main loop populates dictionary keyed departments. values of dictionary dictionaries keyed categories ("no show", etc.). values of those dictionaries counts after. @ end of code show how can extract data data structure:
function makecountdict(categories variant) dictionary dim d new dictionary dim long = lbound(categories) ubound(categories) d.add categories(i), 0 next set makecountdict = d end function sub makedepartmentcounts() dim dcounts new dictionary dim r range dim dept variant, cat string dim categories variant dim long, n long dim report string categories = array("no show", "appt", "can", "walk") n = range("h:h").rows.count n = range("h" & n).end(xlup).row 'last used row in column h = 2 n dept = trim(cells(i, "h").value) if not dcounts.exists(dept) dcounts.add dept, makecountdict(categories) end if cat = trim(cells(i, "c").value) dcounts(dept)(cat) = dcounts(dept)(cat) + 1 next report = "report:" each dept in dcounts.keys report = report & vbcrlf & dept & ": " = 0 3 cat = categories(i) report = report & cat & " = " & dcounts(dept)(cat) & iif(i < 3, ", ", "") next next dept msgbox report end sub to test created random data in columns c , h had format of linked picture ran it. output:
department 5: no show = 1, appt = 1, can = 1, walk = 2 department 3: no show = 5, appt = 2, can = 1, walk = 2 department 4: no show = 2, appt = 1, can = 0, walk = 1 department 2: no show = 2, appt = 1, can = 2, walk = 1 department 1: no show = 1, appt = 1, can = 0, walk = 2 this shows order of keys when iterate bit random -- have j = 1 5 loop rather each dept in keys loop.
Comments
Post a Comment