Need help with index function - combining rows.

[psgolfer32381]

Hi,

I actually had help with something similar here before, but I'm faced with the same problem, and I cannot get it to work. I have a spreadsheet with store numbers down column a and items going all the way across. Each store takes up multiple rows (ie. store 5 spans 10 rows or so). I need to combine these rows so that each store is on one row, combining the totals all the way across. Previously, somebody helped me to create an index and then used sumif on the values. I cannot get the index function to work! I've attached my sheet. The 'sorting' tab is the one that needs to be condensed. I've made the last tab, 'grouped', to do the function on. Please help!

Thanks
Michael

[BB1972]

Michael,

This might not be exactly what you're looking for, but I believe it will give you the information you require.


In AD2:

Please Login or Register  to view this content.

will sum C2 to AC2. Copy this formula as far down your sheet as you require.



In AE2:

Please Login or Register  to view this content.

will find the sum of the cells in Column AD where the corresponding cells in Column A match the value of A2 (ie, "5". Copy this down as far as you did the previous formula.



In Column AG/somewhere/anywhere, make a list of the unique store numbers. In the attached, AG2 contains store 5, AG3 store 30, etc.



In AH2:

Please Login or Register  to view this content.

will look for a match for AG2 (5) in Column A, and will return the value from the corresponding cell in Column AE.

INDEX/MATCH only returns a value for the FIRST match it finds, so no need to worry about values being repeated.



Hope this helps.

[psgolfer32381]

I need the rows to all collapse to a single row though with the collapsed rows totaling all the way across so that I have a single value in each column all the way over. I need to know how many of each item each store will get, and I need to keep the format pretty much the same. We're working with other people's data. I'll attach the one that somebody helped me with before, and maybe that will give you an idea. Thank you so far. The one I attached has the grouping tab done the way I'm trying to do it.

[BB1972]

Michael,

I see bebo helped you here http://www.excelforum.com/excel-form...59#post3088059 about a month ago. I can see what he did with the SUMIF formula, but I don't know how he managed what he refers to as the "difficult" part.

You should bump the thread so that s/he might see your question, or maybe drop a PM - it's easy enough to overlook posts one has been involved in - and hopefully bebo can explain what was done to sort your information, which in turn would mean you don't have to ask the forum every time this exercise comes up for you. For my part, I'm not going to try to reinvent the wheel, when another forum member has already cracked what you want to do.

Good luck.