Excel convert multiple columns to dataset based on unique timestamp-CodePudding

I want to convert(Formula or way to do it) excel from one output to another for google maps csv upload to plot data on maps. Example: Original CSV:

Expected output for mymaps API:

Also note that this coordinates are not constant and changing across the city or state.

Attempt 1) Manual but dataset is too large Attempt 2) Text to Column but that only supports via delimiters

CodePudding user response：

F2  =UNIQUE($B$2:$B$20)
G2  =FILTER($C$2:$C$20;($B$2:$B$20=$F2)*($A$2:$A$20=G$1))
H2  =FILTER($C$2:$C$20;($B$2:$B$20=$F2)*($A$2:$A$20=H$1))

CodePudding user response：

With O365 you can try the following in E1 and you can get the entire result including the header:

=LET(id, A2:A5, time, B2:B5, str, C2:C5, idUx, SORT(UNIQUE(id)), 
 timeUx, UNIQUE(time),GET, LAMBDA(tt,ii, XLOOKUP(tt&"|"&ii, time&"|"&id, str)),
 REDUCE(HSTACK("ref_time", TOROW(idUx)), timeUx, LAMBDA(ac,t,
  VSTACK(ac, HSTACK(t, GET(t,INDEX(idUx,1)), GET(t, INDEX(idUx,2)))))))

Here is the output:

Check the following question on how to use REDUCE/VSTACK pattern to generate each row: how to transform a table in Excel from vertical to horizontal but with different length. We use GET user LAMBDA function to avoid repeating the same calculation with different inputs (tt,ii). Just update the input range names (id, time, str) for your real problem. Added "|" to concatenate the search for more than one value, to avoid any false positive. Check @JvdV answer for more detail and comments. It can be avoided using MMULT, but it produces a more verbose formula.