I’m currently using LibreOffice 5.1.2.2 and am trying to consolidate some data.
What i’m starting with is (very) roughly in the format
Identifier, Name, ID
A, Adam, 1
B, Bob, 2
D, Doug, 4
E, Eve, 5
and
Identifier, Amount1, Amount2
A, 2, 1
B, 1, 1
B, 2, 7
C, 4, 0
C, 0, 2
E, 1, 3
I want to combine that all together. The ideal end result would be something like
Identifier, ID, Name, Amount1, Amount2
A, 1, Adam, 2, 1
B, 2, Bob, 3, 8
C, , , 4, 2
D, 4, Doug, 0, 0
E, 5, Eve, 1, 3
A Pivot Table seems like the obvious way to do so, but I can’t get it to deal with the text value for the Name.
The ID is easy, i can just select the Max for that, but none of the functions I’ve tried return anything for text values, except the first Count, which just results in a 1 for all text values. And of course if i add the “Name” value to the Row Fields it then creates two rows for each Identifier, one with the Name and ID but no Amounts, and one with Amounts but no Name or ID.
I realize that applying some of the functions to text wouldn’t make any sense, but I would have thought “Max” would have done something, and since i know there’s only one value for each Identifier in that column it ought to have worked out fine. Failing that why isn’t there a “First encountered value” or something like that? Or have i just not found the right option yet?
This seems like such a simple task, but I’ve been trying to figure it out off and on for over a year, so I figured I would finally give up and ask for some help.
Thanks!