1

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: enter image description here

Expected output for mymaps API: enter image description here

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

Nishil Shah
  • 113
  • 1
  • 7
  • 3
    Please provide your sample data and expected output as text within your question - ideally using [Markdown Table Generator](https://www.tablesgenerator.com/markdown_tables) (read [this](https://meta.stackoverflow.com/a/285557/16578424) for an explanation). It is much easier to help you then. – Ike Jan 16 '23 at 18:27

2 Answers2

1
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))

enter image description here

WeAreOne
  • 1,310
  • 1
  • 2
  • 13
1

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: excel 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. Due to the nature of your data, I don't think it is necessary, using a delimiter will be enough.

David Leal
  • 6,373
  • 4
  • 29
  • 56