Removing NULLS and Duplicates in each Row to leave one set of values per row

[Chris3312]

Hey guys, wondering if you would have any ideas as to a method to turn my data that looks like Table 1 into an end result in Table 2. Basically for each row I only want one set of names, getting rid of duplicates and NULLS from the other four columns. However I'm not sure how to do this as the two names could be anywhere in those first 6 columns. (There will always be a full name grouped together by the number eg. if Name3 is present, so will LastName3)

Name1 Last Name1 Name2 LastName2 Name3 LastName3 Value

   Jack      Smith         NULL      NULL         Jack       Smith        50

  Leslie     Jules         Leslie     Jules          Leslie      Jules         55

  NULL     NULL        Daniel    Raddoff       NULL      NULL        15

Name1 Last Name1 Value

  Jack       Smith          50

 Leslie      Jules           55

 Daniel   Raddoff         15

Any help would be appreciated.

[jdunkerley79 · Accepted solution]

I'd suggest:

1. Add a record ID

2. Transpose to switch rows to columns grouped by record ID

3. Filter empty cells

4. Use a Regex to get the core field name (Name or LastName)

5. Use a Cross Tab picking first value to make table again

6. Use a select to reorder and drop record id