My table (~70k rows) has data in columns/rows out of order as so:

ID	LASTNAME	FIRSTNAME	PLACE	PROBLEMS TO FIX
ID: 0	Last Name: Williams	First Name: Tracie	Place: US	CORRECT DATA
ID: 1 Last Name: Gregory	First Name: Eric	Place: US		ID & FIRST NAME COMBINED CELL
ID: 2	Last Name: Buchanan	Place: UK		MISSING FIRST NAME IN COLUMN C
ID: 3	Place: UK			MISSING LAST NAME IN COLUMN B
ID: 4		Last Name: Cormick	Place: UK	LAST NAME IN WRONG COLUMN (FIRSTNAME)

Issues Screenshot:

Here is the expected result I got by applying the manual steps in the "FIXES" Column / D column:

ID	LASTNAME	FIRSTNAME	PLACE	FIXES
ID: 0	Last Name: Williams	First Name: Tracie	Place: US	CORRECT DATA (NO FIXES)
ID: 1	Last Name: Gregory	First Name: Eric	Place: US	B3: INSERT CELL AND SHIFT TO RIGHT 1 CELL A3: CUT CELL A2 FROM "LAST" TO END OF CELL A3 B3: PASTE FROM "LAST" TO END OF CELL A3 INTO CELL B3 A3: IN CELL A3, DELETE FROM "LAST" TO END OF CELL A3
ID: 2	Last Name: Buchanan		Place: UK	C4: INSERT CELL AND SHIFT TO RIGHT 1 CELL
ID: 3			Place: UK	B5: INSERT CELL AND SHIFT TO RIGHT 2 CELLS
ID: 4	Last Name: Cormick		Place: UK	B6: DELETE CELL AND SHIFT TO THE LEFT 1 CELL C6: INSERT CELL AND SHIFT TO RIGHT 1 CELL

Fixes Screenshot:

The manual fixes work fine but it is very time consuming.

1st alternative to manual solution:

As a first alternative solution I thought of maybe recording macros, then calling those macros in a Google sheet formula.

I have something like following pull-down formula (with pre-recorded macros as value_if_true variable) in mind (in Column B, and similar on for the other columns):

=IFS(
    REGEXMATCH(B1, "First Name:"), CALLMACROSHIFTRIGHTONECELL,
    REGEXMATCH(B1, "ID:"), CALLMACROSHIFTLEFTONECELL,
    REGEXMATCH(B1, "Place:"), CALLMACROSHIFTRIGHTTWOCELLS
    REGEXMATCH(B1, "Last Name"), "",
    TRUE, "")

But I'm not sure if that's possible and couldn't find any documentation on calling macros in Google Sheets formulas.

2nd alternative to manual solution:

If there's no way to perform the 1st alternative currently in google Sheets, I thought next of using a script by calling the Fixes steps in Google Apps Script Functions.

But I can't find the documentation about INSERTING CELL AND SHIFT TO RIGHT command:

or DELETING CELL AND SHIFT TO LEFT command:

and CUTTING A CELL PORTION AND PASTING IT IN ADJACENT CELL:

If you know of a more efficient way to achieve the expectd result (known script or techniques) or if you know references to the needed Google Apps Script documentation, I'll be very grateful if you'd like to share it.

CodePudding user response：

If it's useful, you can create a formula that will "accomodate" the data in other columns if it is as "standard" the labels as your example:

=BYROW(A2:D,LAMBDA(each,LAMBDA(r,IFNA({
  "ID: "&REGEXEXTRACT(r,"ID: (. ?) [Last|First|Place]"),
  "Last Name: "&REGEXEXTRACT(r,"Last Name: (. ?) [First|Place]"),
  "First Name: "&REGEXEXTRACT(r,"First Name: (. ?) [Place]"),
  "Place: "&REGEXEXTRACT(r,"Place: (. )")
}))(TEXTJOIN("  ",1,each))))