Truck Number | Job1 | Job2 | Job3 |
---|---|---|---|
71 | 5928 | 5928 | 5928 |
72 | 3958 | 5928 | 2971 |
73 | 2971 | 5928 | 2971 |
This table is filled in automatically from our daily dispatch sheet to break down a driver's route. In my table, each truck number gets a row, and each job they complete gets a column. I need to be able to fill in a separate table where the job number is the column header (these are be permanent values) with each truck number that ran that job below. the truck number needs to be returned as many times as the job number appears.
For this example, this data set should return:
2971 | 3958 | 5928 |
---|---|---|
72 | 72 | 71 |
73 | 71 | |
73 | 71 | |
72 | ||
73 |
The order these appear in is not important, but if the value appears multiple times, the truck number must be returned multiple times.
I have tried every lookup, match, and index formula I can think of and I can't seem to find anything that will search multiple columns to do what I need or, if it does, it wont return the value multiple times.
I'm open to a VBA option, but for the most part, my experience there is limited to copy/paste & clear functions. Thanks in advance for any help!
Using Dynamic array formula:
=LET(
rng,B2:D4,
trk,A2:A4,
title,SORT(UNIQUE(TOROW(rng),TRUE),,,TRUE),
ptbl,DROP(REDUCE("",title,LAMBDA(z,y,HSTACK(z,TOCOL(IF(rng=y,trk,NA()),3)))),,1),
tbl,VSTACK(title,ptbl),
IF(ISERROR(tbl),"",tbl))