Excel: How to bind/group several data points to one so that if the arrangement is changed, the data points remain fixed to the one?

I'm not sure if this the best place to ask this question, but I need a little help. This is probaby a simple fix, but I'm at a loss at what to do. I'm using Excel to sort out which rooms in the building, where I work, have internet ports and which of the internet ports have internet switches. Most of the rooms have several internet poorts. So I've created a table with the headers: Room, Internet ports, Internet Switch. Is there a way to group/link the Internet ports to the room, without having to write the room number beside each Internet port, so that if the order of one column is changed, the data points don't shift from their groups?

For example:

If I were to reorganise the data in accordance to ascending or descending room number (there is a 438 with different Internet port numbers), I would have a list of room numbers with only one Internet Port beside them and not the rest. Hence my question: is there a way of fixing it so that a group of Internet ports stick with one room, without having to write the room number beside each entry?

Any help for a novice much appreciated.

As a self-described novice you have, unfortunately, fallen into the trap of mixing data and presentation because of the way you have chosen to organise your data.

The way you have organised your data makes it easy to see which ports are in each room. That's the presentation part. However, as you have determined, if the order of rooms is changed in your list of data, it is then messy to re-organise the data and preserve the the original style of presentation. That's the trap of mixing data and presentation.

The way out of this dilemma is to separate data and presentation. To do this first, you need a one-off exercise to organise your data into a proper list.

I shall assume that your existing data is in columns A and B of a worksheet with: cell A1 containing the text "Room", cell B1 containing "Internet Port" and, cells A2 and B2 containing your first row of data with values in both cells. Cell A3 may or may not contain a value depending on whether or not your second row of data refers to the same room as the first row.

The "proper list" will be created in columns D and E with the following steps.

1. Copy the range A1:B2 to cell D1. You should now have a copy of the header row (containing "Room" and "Internet Port") in cells D1 and E1 and a copy of the first row of data on cells D2 and E2.

2. In cell D3 put the formula =IF(ISBLANK(A3), D2, A3) and in cell E3 put the formula =B3. This should cause the range D3:E3 to display your second row of data, with cell D3 showing the value of the room even if it is missing from cell A3.

3. Find the last row number of your data, by looking in column B. Suppose it is in row 127 of the worksheet. Copy the range D3:E3 and paste to range D3:D127 (replace the 127 with the final row number of your data). You should now have a copy of your data in columns D and E, displayed as a "proper" list, with room numbers showing to the left of each internet port.

4. The list in columns D and E is based on formulae and is dependent on what is in columns A and B. Changing a value in column A or B would cause the values in column D or E to change. So the final step is to break this dependendency.

5. Copy the range D1:E127 (again replacing the 127 with your final row number - see step 3) and then Paste Special to cell D1 using the "values" option. This will convert all the cells in the copy of your data in columns D and E from formulae to values, giving you an independent copy of your data in a list format.

You can now sort, add to, and delete from and this list of data items. The point here is that an item of data in the list comprises a row with, in this case, a pair of values which are the Internet port and the room in which that port is located. You can now ignore what is in columns A and B and once you are satisfied that the list in columns D and E properly reflects what is in columns A and B I would recommend deleting columns A and B. (Having multiple copies of data is another novice trap - eventually the copies will become inconsistent and questions will then arise as to which is the correct set of data and why are incorrect sets being kept.)

Once you have your data in a list format (in columns D and E) it can be presented in your original style using formulae. The steps below perform this presentation in columns G and H.

6. Put the header values of "Room" and "Internet Port" into cells G1 and H1, respectively. In cell G2 put the formula =D2 and in cell H2 put the formula =E2.

7. In cell G3 put the formula =IF(D3=D2, "", D3) (the "" is two double quote characters with nothing between them) and in H3 put the formula =E3.

8. Copy the range G3:H3 to G3:G127(again replacing the 127 with the final row number of your data).

That's it. Columns G and H should now display the data in columns D and E in the presentation style set out in the original post. If any value is changed in columns D and E, these changes should be reflected in the presentation of the data in columns G and H. If you re-order the data in columns D and E, the presentation in columns G and H will show a room number in column G if and only if there is a change of room showing in column D.

If you have successfully followed the 8 steps above, then you will have separated your data from the presentation of that data and, hopefully, learnt a valuable lesson from data management 101.

The OP mentions internet switches as well as Internet ports and room numbers but, unfortunately, omits the switches from the pictures of the data. However, the switches are, presumably, just an additional column of data and the prínciples of separating data from presentation can be readily extended to incorporate this additional column. Doing so would make each item in the data list have 3 values: internet port, internet switch and room number. The presentation would presumably reflect any one-to many relationships between these 3 data attributes, just as the presentation in the OP reflects a one-to-many relationship between rooms and internet ports (that is, one room can have "many" [more than one] Internet ports).