Group a single row cell to multiple row cells

[thb]

Hello all,

I'm sure there's some very simple solution to this, but I'm not seeing it:

I am trying to group columns together that have an uneven set of rows. I want to be able to sort by Column C, to show all the Names and Websites associated with Item 1.

Any way to "merge" or "group" and maintain sorting capabilities?


Column A | Column B | Column C
Name A Website A Item 1
Website B
Name B Website C Item 2
Name C Website D Item 1
Website E
Website F

Thanks so much in advance!

[NBVC]

Try this method, to populate the empty cells:

Select column A and then hold the CTRL key and select A1.

Then go to Edit|Go To and click Special....select Blanks and click Ok.

enter an = sign and hit the up arrow key.

Hold the CTRL key again and hit ENTER.

You may have to fix whatever was in A1.


Repeat for column C, etc...

[thb]

That is a very cool trick, thanks for showing that to me. However, is there a way to keep my column A as single lines? Basically, I'm being asked to have a single line of Column A Names and then have a list of websites associated with a single name. For some reason, we don't want to sort with multiple Names... Any other way? I thought I could perhaps merge cells in column A to have that one cell associated with the multiple cells of Column B, but you can't sort that way...

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[thb]

Sorry! Please see attached and thanks again!

[NBVC]

Besides what I have already showed you, the only other way really is to add helper columns that basically fill in those cells with what's above.

So, in D2 enter:

=LOOKUP(REPT("z",255),A$2:A2)

copied down

and in E2 enter:

=LOOKUP(REPT("z",255),C$2:C2)

Now include these columns in your filter and use them instead to filter the data.

[Miraun]

To tack on the NBVC's comment, after you've added the additional information to the raw data, you can run a pivot table off of the data and easily extract it in a way that's pleasing to the eye, or the end stakeholder.

The problem is that you're trying to make your raw data appealing for reporting. Excel is great at generating reports off of raw data, but not always for making raw data look good.