Excel: Trying to create a sortable table with 2 rows connected to each individual row in the table

Question

I really don't know how to explain this and I cannot post the picture I made because I'm new. But here goes.

What I'm trying to do is have a table in columns A and B which skips every other row when sorted. What happens at the moment is all of the data I entered in these two columns where I want the data to remain end up filling in all the rows that I intentionally left blank.

My spreadsheet looks somewhat like this

        Column A  Column B  Column C  Column D 
Row 1   Size      Qty                  Week 1
Row 2   8.5       5         Issued       3
Row 3                       On hand      2
Row 4   9         6         Issued       1
Row 5                       On hand      5

Note that Columns C and D will not be a part of the sortable table, the data entered in column D is just for an example; I intend for it to remain blank and to be written by hand throughout the month.

Now, I realize that merging cells (like A2 and A3 together, B2 and B3 together, A4 and A5, B4 and B5) is big no no when it comes to tables but I don’t see how else I can achieve what I'm trying to do.

Also, I'm not sure if it matters, but I need the cells in Column A to maintain their data validation capability.

-Wil

possible duplicate of Excel: How to treat multiple lines as one while sorting? or Grouping and sorting rows — G-Man Says 'Reinstate Monica', Oct 11 '14 at 00:51

score 1 · Answer 1 · answered Oct 10 '14 at 06:14

If I'm understanding your problem correctly then the problem is because your rows are not being used as records. This means that you cannot sort on them. If you want to fix this then you need to change your layout a little. Consider using this layout:

        Column A  Column B  Column C    Column D 
Row 1   Size      Qty       Issued Wk1  On hand Wk1
Row 2   8.5       5         2           3
Row 3   9         6         34          1

Wil Marsh · Answer 2 · 2014-10-10T08:04:45.573

Thank you for the prompt reply.

You’re solution would get the job done but the layout I have right now would really be more sufficient for what I intend to use it for.

Perhaps I can clarify a bit. I’m essentially looking for something like this, if there’s a way it can be done.

        Column A  Column B    Column C
        Size                   Week 1
Row 1   5        (Row 1 A)    (Blank)
                 (Row 1 B)    (Blank)
Row 2   6        (Row 2 A)       ''
                 (Row 2 B)       ''
Row 3   3        (Row 3 A)
                 (Row 3 B)

Note that I took off the original Column B (Qty) that I had. I realized I don’t really need it.

So Column A will really be the only one that needs to be sorted. The reason is that if I get a new size shoe I can simply fill in the new size at the bottom of the list and hit sort at the top to have it fall into its correct place.

As for the Columns C, D, E and so on, they won’t need to be sorted because they will remain blank.

Is there a way to have “sub” rows like I illustrated in Column B?

I think I can post a picture now...

Inventory Spreadsheet

I would do it as two pieces. Store the data as @krowe suggests and sort and maintain it there. Have a second sheet for viewing output that is formatted as you want for that purpose. The output sheet could be generated in the style of a "report" (do an action that produces the output based on a snapshot in time of the data), or be actively linked to the data using generic reference formulas that never have to be changed. — fixer1234, Oct 10 '14 at 16:11

Excel: Trying to create a sortable table with 2 rows connected to each individual row in the table

2 Answers2