1. M

    Is there a way to create an "If Then" Group By in Power Query using parameter values?

    I am trying to create a Power Query with a parameter that what would perform the Table.Group function based on the parameter value. For example, if the parameter value Group by Region= "Yes", then sum up the sales and profits for each region. If the parameter value does not equal "Group by...
  2. G

    Systematically add 150 more rows every 51 cells down

    I have a sheet that has groups of 50 rows with a row to separate each group from the next. I would like to increase the amount of rows in each group by 150 (a total of 200). So starting on cell ("D55") add 150 rows above D55:AA55, then go down 51 rows and repeat for cell ("D106") [add 150 rows...
  3. B

    Conditional change: Change value in a cell based on specific text (ending in "X") in another column

    Hello Board, I am new to board and dumb, hoping someone would share a simple VBA code I have a large table of data of ~10000 rows. I'd like VBA Code to loop through: 1) evaluate data in column A for cells that contains 5 characters ending in letter "X" 2) If it finds such, I'd...
  4. C

    Grouping Rows

    Hi all, I want to group rows in blocks of 2 rows at a time. For example...row 1 & 2 would be a group. row 3 & 4 would be a group. so on and so forth. I want to use the 1 & 2 button to open and collapse the groups. I had it before but can't for the life of me remember how to do it again. I have...
  5. S

    Formula to compare 2 cells with different text

    So I am trying to create a formula that compares to different cells to make sure the area assigned is correct. The problem is how the text is written ... Cell B1 the text reads "Group 55 Region E Area 558" in Cell B2 the text reads "55E8" I need a formula that compares B1 to B2 that shows 55E8...
  6. S

    If X in C4, copy to E4, Dependent Validation

    I've set up Dependent Data Validation so when a specific Session is selected only specific options are available in the next two columns. However, the third column the user is to input a number. The only restriction is for the Session called Previous Group. Those two dependent validation columns...
  7. S

    =ROUNDUP(RANK($C27,Rando)/Sizes,0) FORMULA

    I tried the formula below =ROUNDUP(RANK($C27,Rando)/Sizes,0) and it works great for a list of people. I need this formula to work for a group of employees rather than a list of names. My chart below shows a group of employees in each department. So 42 represents one department, 19 another group...
  8. G

    Creating a group for items in a listbox

    Is there a way to create groups of line items from the listbox? I have a userform that allows me to show and hide certain columns. There are 2 listboxes in the userform; one to 'Show' the list item and the other to 'Hide' the list item. By double clicking the items in the listboxes, a user can...
  9. S

    Average groups of numbers within a larger group

    I have a large group of numbers in a column. I would like to average those numbers in groups of 7. So average the first 7, average the 2nd 7, and so on down the whole list. Is there a way to do this without manually selecting each group to find their average? I have Kutools and ASAP utilities...
  10. N

    Custom group handling in a pivot table

    I would like to ask you about the groups in a pivot table. You easily create one group by selecting for example 5 from 6 columns headers (or rows, of course) in the pivot table and right-click - group. From this point and then: If I see a grouped pivot table how can i know which columns it...
  11. S

    Help to Select Case with dynamic range

    Hi All I want to have a Function with select case that: range value is range(A2: last row) and group by range(B2:lastrow) like below table have 04 group: The code of function like this: Funtion () for i = range(A2: lastrow) select case group by range(B2:last row) Case a = Array("B004") 'group...
  12. L

    grouping sheets and inserting a picture/shape

    Hi I wanted group several sheets and then insert a picture or shape in one sheet and hoping it will appear on all grouped sheets but when I group sheets, insert picture or shapes options are grayed out. Is that by design? Thank you.
  13. P

    Multiple Dynamic Lists

    I’m trying to set up a table where you can have multiple dynamic lists. For example, column 1 has a drop-down list containing the asset group, column 2 would then have a sub-category if there is one otherwise it would remain blank and column 3 would then show the correct assets attributable to...
  14. TAPS_MikeDion

    Can you change a button group lock options to True/False with 1 command?

    Just wondering if I can shorten my code a tad by changing grouped buttons with 1 command instead of two. For example: If you have a group of two radio buttons (for this example I'll call the group "NewEmp") and you want to lock or unlock them, do you have to use two commands (i.e...
  15. A

    Report Lookup Data

    Hello, I am struggling with a solution to select data within cells and skip cells that are blank. The following is a description of what I have going: Group 1 - Book Size 6 cells in 1 row Cell shows the size based on selection made. The selection can require up to 3 sizes. Group 2 - Book...
  16. JenniferMurphy

    Best way to skip 1st & last cell in range in UDF?

    I have a little UDF that does some tallies on a column of cells (B2:B5). <tbody> R/C A B 1 Group Rating 2 North 25.2 3 South 14.7 4 East 52.0 5 West 33.8 6 Average 31.4 </tbody> I call the UDF from another cell like this: =Tally(B2:B5) I want to change that to include the cell...
  17. J

    Pivot table grouping into months/ years not working

    hello please can someone help me. I am using Excel 2016 and in my pivot table, I tried to group my date rows (example 11/12/2018) into months or years. However when I click on 'Group Selection', it automatically assigns Group 1, rather than giving me options to sort into years and months. I...
  18. O

    Need help with Find Method

    I have four columns with over 400,000 rows of data labelled (Year, Avg, UIC, Group) that look like (2013, 1.563, 0011, 23). I want to distribute them into 322 separate matrices, one for each Group that have columns labelled by the year: 2013 to 2019 and rows labelled by the UIC from 0001 to...
  19. R

    Lookup Question

    Hi All, I hope I can explain this clearly. Column A is Student Name. Column B is group (either group 1, 2, 3 or 4). Column C is test mark out of 100. I have a lookup for each group. But each group has different parameters. For example Group 1: 0 to 40 = Below Target. 41 to 65 = On Target. 66...
  20. A

    First time creating macros for complicated sorting

    Hey, all! Just a bit of background: I work in sales at a hotel. I've been tasked with manually entering our numbers into a new system until it interfaces with the program the front desk uses to keep track of rooms. It's daunting. I'm given an extremely dense Excel file with information about...

Some videos you may like

This Week's Hot Topics