SUMIF function possible with this example or Macro?

Hello All
I was wondering if a SUMIF function could be use to solve this problem I am having or do I have to use a macro of some sort?

I have people who are assigned to work in a specific departments .The Bolded Text is the Dept (cell A1 ) and the roster of employees fall under that bolded text Cells A2-A8)
I would like to have the persons name added to the dept name in coulmn B and at the start of a new Depratment the formula would have to know to add the Name of the employee to the next Bolded Department.

I attempted to start this using a simple formula, but it would take me forever, and was wondering if SUMIF could help?

Thanks in advance

You can do it with a formula (well, 3 actually), but it doesn't involve SUMIF.

First of all, put this formula in cell C1:

=IF(A1="","",MID(A1,FIND("-",A1&"-")+2,2))

This should be copied down to the bottom of your data (i.e. to cell C1024 in your file). Then you should enter the number 1 into cell D1 and put this formula in cell D2:

=IF(OR(AND(C2="10",(LEN(A2)-FIND("-",A2&"-"))>9),AND(C2="31",(LEN(A2)-FIND("-",A2&"-"))>7)),MAX(D$1:D1)+1,"")

and again, copy this down to the bottom.

Then you can use this formula in cell B2:

=IF(A2="","",IF(D2="",A2&" "&INDEX(A:A,MATCH(LOOKUP(9999,D$1:D2),D:D,0)),""))

and copy this down. It seems to produce the correct results for all your data, except for the block that follows row 916, and that is because you have two hyphens in cell A916. If you change the first hyphen to a space, then it works ok.

You can hide columns C and D if you don't want to see them on your sheet.

Hope this helps.

Pete

Thanks for the rep, Ron.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

WOW ! Thank you so very much Pete. You are a lifesaver. You have no idea how much time you gave back to my life

I was wondering if you could spare a few moments to explain to me what you are doing with each formula. I like to learn

Thank you again ! I am very grateful !

Hi Ron,

I will do, but it is getting a bit late here in the UK (nearly 2:00am), so it will have to be tomorrow.

Cheers,

Pete

Thank you Pete ! Sleep well

Hello again Ron,

I needed a way to determine which rows held the department name, as a formula cannot be used to detect formatting (i.e. bolded). I noticed initially that the departments seemed to have a 9-digit number at the end after a hyphen and space, and that these all seemed to begin with "101". The staff names also had numbers after a hyphen and space, but these seemed to be 4-, 5- or 6-digits long. So that got me thinking that I could extract the final part and examine that. Looking further down your data, though, I saw that some numbers for departments began with "102", so maybe I could just look at the first two digits to see if they were "10". Even further down the list, however, the department numbers became 7-digit, and began with "31".

So, the formula in C1 uses the MID function to get characters from the middle of the text in column A - it looks for a hyphen, then extracts characters which are two positions away from that (to account for the space), and then takes two characters.

The formula in D looks at those two characters in column C to see if they are either "10" or "31". But, that's not sufficient on its own, as there could be a staff number which begins with those digits (see, for example, row 7 or row 15), so another check is done on the length of the numbers (i.e. 9 characters for "10", or 7 characters for "31"). If either of those two cases is correct then that row is for a department name, and a sequential numbering is set up by adding 1 onto the largest number generated above (remember, D1 is set to 1). If the row relates to a member of staff, then the cell in column D is set to blank. You can see the effect - sequential numbers appear down column D, but only on the rows which contain department names, not staff names.

To understand what's happening with the formula in column B, you could put this part of that formula in cell F2:

=LOOKUP(9999,D$1:D2)

and copy it down say 20 or 30 rows. On any one row, this will return the last number that was previously encountered from column D. So armed with that, we can find out which row that occurred on and extract that cell from column A (i.e. the name of the department). The formula in B2 looks at D2 and if it is empty it will return the contents of A2 and add onto it the name of the department. If the cell in column D contains a number, then it must be a department name, so the formula in column B returns a blank.

Hope this helps.

Pete

Genius ! Thanks for taking the time to explain Pete. I am so thankful ! Have a great week
Ron