Populate from Forms for Excel return by date and reference

Visitor

We have 800 assets that are mobile between sites.  Every site completes a return each month to say which assets are at each location.  The return is on a Forms for Excel with the simple layout: Location (option), Asset 1 (number), Asset 2 (number), Asset 3 (number)...

 

I'm trying to create an output tab that lists all the asset numbers as rows, and the month/year as columns, and populates the reported location for each asset in each month (so when we need to do compliance testing we know where to look!).

I want the sheet to be entirely automated so we only have to interact when we need to find an asset.

 

  1. How do I automatically turn the Form return for a number with the apostrophe into a usable number
  2. Is this some Index/Match function or a Pivot table or something completely different?

The output form the form looks like this:

ID   Start    Completion    email    Name    Location    Asset1    Asset2    Asset3 ...

1     4/4/22     ###                Email address removed  Alan   Loc-B              '2            '742          '355

2     4/4/22    ###                Email address removed  Ben     Loc-F              '3             '51            '4

3     1/5/22    ###                Email address removed  Alan   Loc-B              '742         '4              '1

 

I'd like the output to look a little like this:

Asset    Apr22    May22    Jun22 ...

1                          Loc-B

2          Loc-B

3          Loc-F

4          Loc-F       Loc-B

 

I hope that's clear enough, and I look forward to learning what you whizzes recommend!

0 Replies