Excel: condensing information

QuestionEdit

I start with a spreadsheet containing rows of information for individual telecommunication wall plates. Including the room number, number of cables present and labels for those cables as well as some other information that doesn't matter to this like a handle and manually assigned tag. There are anywhere from 1 to 6 cable labels per plate, and up to 500 or so plates. I need to create a table-array containing every cable label, but rather than displaying the actual cable label for a cable it needs to be the room number.

Room# Cable ID 112 1A-1A19 1A-1A20 113 1A-1A21 1A-1A22 1A-1A23 112 1A-1A24 1A-1G06 114 1A-1A25 114 1A-1A26 1A-1A27 1A-1A28 1A-1A29 116 1A-1A31

this would be organized as follows 1A-1A19 1A-1A20 1A-1A21 1A-1A22 1A-1A23 1A-1A24 1A-1A25 1A-1A26 1A-1A27 1A-1A28 1A-1A29 1A-1A31

this would then output to look like this using the room numbers. 112 112 113 113 113 112 114 114 114 114 114 116

notice there is only one "112" between the 113 and 114, the other 112 would be placed further down the list where it would fall alpha-numerically. And 1A-1A30 does not appear and there for is left blank in the room number table-array.

I can send a sample .xls file if I didn't give you enough information. In the image the labels on the right side should then be replaced with their respective room numbers from the left side.

AnswerEdit

Brian,

Yes, send the file. I can't read you image as everything is way too small.

I would need to know why your output is organized as it is in terms of the intermediate results of the cable labels. Why 6 in the first row, 5 in the second row (6 with the missing cable label) and 1 in the third as an example.

I understand that you also want the cable labels sorted alpha numerically. As far as missing labels, you example if fairly obvious, but are all labels as obvious?

So send the file with a complete explanation of what you want.