multiple rows down to one row - transpose not the solution

I have a 107k plus line spreadsheet with multiple lines per material as each material is extended to multiple sales organizations (countries). I have managed to get the piece of info I need into columns by sales org but still on individual lines. I need a way to put all of these values on one line by sales org. Also, I don't know macros or VBA so I need a formula way to accomplish this. Attached is the before and after that I need.

It strikes me that you need a unique list of materials - do you already have that? Your example file shows only one material for different sales orgs, so it is not much use in testing a more generalised solution to you problem. Can you submit another example file with a bit more data in it?

Pete

Absolutely happy to. I appreciate it. See if this is more helpful.

I don't think there is an easy way of doing this using formulae. As you are dealing with text values, you will have to carry out multiple concatenation (with mainly blanks being joined to the one value within the range for a particular material), and Excel is not very good at doing that. In your example data, similar materials are grouped together - is that always the case in your real data, or have you just arranged it like that for the example?

Pete

I have a co-worker who has accomplished this in the past. Unfortunately he is tied up on a project.

You have filter drop-downs in columns O to BK, but no data in those columns. Is this because you have deleted irrelevant data from those columns in your real file? I want to use a helper column to identify the last record for each material type, and I need to know which column to use.

Pete

Just columns I neglected to delete and not needed.

Okay, well I've used column O as the helper and it wasn't as difficult as I first thought (see attached file). It is important to put zero in cell O3 and then you can put this formula in O4:

Formula:

Please Login or Register  to view this content.

which can be copied down to the bottom of your data (I've shown it in blue). Then insert a new sheet (Sheet1) and put the headings on rows 1 and 2 with this formula in A3:

Formula:

Please Login or Register  to view this content.

this returns the first material number, and you can copy the formula into B3 to get the Material Description. It relies on the data being sorted by material number. Then put this formula in C3:

Formula:

Please Login or Register  to view this content.

and copy that across to K3. Then you can copy the formulae from A3:K3 down as far as you need to (until you get hyphens in columns A and B - I've copied to row 14 in the example). This will give you a unique list of materials with the breakdown as requested.

There is something strange about the data in your posted file, though. If you highlight the cells F4:N22 on Sheet2, then press F5 (GoTo), then click on Special and on Blanks, then you can see that not all of the empty cells are selected. This suggests that they are not empty (and this was throwing out my formula for the first material), but if you enter =LEN(F4) in a blank cell you get 0, and =CODE(F4) returns #VALUE, both of which indicate that they are empty. Furthermore, if you select one of the cells and click on the formula bar as if to edit it (or press F2) then press <Enter> without doing anything, then the cell does appear to be blank if you repeat the F5 exercise. I don't know what's happening here, but I had to delete those cells in order to get the final formula to work (as it looks for the last non-empty cell in the range).

Anyway, it seems to be working, so you'll need to try it out on your real data.

Hope this helps.

Pete

Thank you! I will print this out and see if I can replicate in the real spreadsheet. I appreciate this!!

Okay, well if it takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thanks I will do that followup and thanks once again!

First formula went great but the second one isn't working. Not sure why so far.

Nevermind I got it! Whew.

Okay, I've got your message, so I'll email you.

Now, remove your email addresses from that last post (unless you want to get a load of spam).

Pete

LOL awesome thanks!

Just checking that you didn't message me yet in case there is any technical problem with receiving on my end.

I emailed you straightaway, so I'll forward it to your other address in case your works system blocked it.

Pete

By the way, did you find out what may have caused the strange behaviour of the empty cells in the file you attached to Post #3? I'll ask if some of the other experts can offer an explanation.

Pete

Thanks for that, Martin - that is probably what happened.

Pete

Yeah, thanks for that - that could be another cause. I don't know how the OP produced the file.

Pete