pull down to refresh
The thing is that ID and service are repeated lines too.
So ID 1234 could be on 4 or 6 lines, but having different services.
This literally switching lines to columns with lookup for 2 references and return the data for each service.
I know I have to make a vlookup + if formula but can't get it working right now.
reply
reply
In which case you would like to sum the Data column?
No I don't want to sum data. Only want to copy data from each service line into a service column.
| ID | Service | Data | ||||||
|---|---|---|---|---|---|---|---|---|
| 1234 | AAA | 4032 | AAA | BBB | CCC | DDD | EEE | |
| 1234 | BBB | 145 | 1234 | vlookup or IF ? | vlookup or IF ? | vlookup or IF ? | vlookup or IF ? | vlookup or IF ? |
| 1234 | CCC | 136,5 | 456 | |||||
| 1234 | DDD | 48 | 658 | |||||
| 1234 | EEE | 56 | ||||||
| 456 | ||||||||
| 456 | ||||||||
| 658 | ||||||||
| 658 |
reply
Does this make sense?
| LKP | ID | Service | Data | ||||||
|---|---|---|---|---|---|---|---|---|---|
| =B2&"|"&C2 | 1234 | AAA | 4032 | AAA | BBB | CCC | DDD | EEE | |
| ^^ Drag this DOWN | 1234 | BBB | 145 | 1234 | =VLOOKUP($E3&"|"&F$2,$A:$D,4,0) | ||||
| 1234 | CCC | 136,5 | 456 | ^^ Drag this down, then drag them all across | |||||
| 1234 | DDD | 48 | 658 | ||||||
| 1234 | EEE | 56 | |||||||
| 456 | |||||||||
| 456 | |||||||||
| 658 | |||||||||
| 658 |
By "Drag" I mean the fact that you can click on the bottom right of the cell and "drag" the formula down. This will update the cell references accordingly and display the corresponding values.
Here is a copy of the spreadsheet: https://paste.nostrdev.com/?d557826675410582#BBFDzTe8txYBzY9xhHNfiNvRNtPVZr4kvVACPCrnNfdN
reply
I get formula error.
Why did you create column A, just to concatenate column B and C? I don't need exactly that, I need only to search and compare.
reply
$sign means that the formula will "stick" to that column (or row) when dragging a formula along or down.