Best to use Automator or Formulas to tidy a terribly formatted sheet

Question

wright1979 Author

Level 1

8 points

Best to use Automator or Formulas to tidy a terribly formatted sheet

My question is I have a really badly formatted numbers table and I am hoping to move the data into a new organised sheet and I'm not sure firstly if it's even possible and secondly what to use to achieve this.

Ok so this sheet was started back in 2006 and the Table wasn't set out correctly in the first place...

I think the best way to explain is using a snapshot!

Each item is grouped with the big border everything attached is between the serial numbers in column A.

B. is the address that needs to be split and joined...

Then theres mobile and telephone numbers a few notes and a purchase date.

Any pointers will be greatly appreciated !!

Many thanks,

Tim

MacBook Pro 13", macOS 10.14

Posted on Mar 14, 2019 12:49 PM

Reply

Answer 1

SGIII

Level 8

35,288 points

Mar 15, 2019 2:21 AM in response to wright1979

Here's an approach using the OFFSET function:

The formulas, filled down from row2 of each column:

A2: =OFFSET(Table 1::A$1,K2,0)

B2: =OFFSET(Table 1::A$1,K2+1,0)

C2: =OFFSET(Table 1::B$1,K2,0)

D2: =OFFSET(Table 1::B$1,K2+1,0)

E2: =OFFSET(Table 1::B$1,K2+2,0)

F2: =OFFSET(Table 1::B$1,K2+3,0)

G2: =OFFSET(Table 1::C$1,K2,0)

H2: =OFFSET(Table 1::C$1,K2+1,0)

I2: =OFFSET(Table 1::D$1,K2,0)

J2: =OFFSET(Table 1::C$1,K2+2,0)

K2: =ROW()−1+3×(ROW()−2)

The OFFSET function works by moving a certain number of cells up/down or right/left from an anchor cell.

The Offset column here calculates how many cells down from row 1 each block starts. It could have easily been manually by starting with 1 and incrementing by 4. The calculation here assumes blocks of 4 lines. If practical, you might consider separating your table into 4 row blocks and 5-row blocks. For the 5-row blocks you would change the values in the OFFSET column to increment by 5 rather than by 4.

The formulas could be made more complicated to suppress the 0's when there is nothing in the relevant cell in the original table. However, I've found it's often easier to "eyeball" the results and clean things up like that manually.

SG

Reply

Answer 2

wright1979 Author

Level 1

8 points

Mar 17, 2019 10:40 AM in response to SGIII

Thanks for getting back to me SG.

Just sitting having a play with this again and I have moved on slightly and wondered if I could ask some more of you!

Attached is a new sheet I have managed to add some more defining columns to table 1 which I hope will help!

id - Plan and simple

col1 - A number unique to each item hopefully so I can determine the rows of each item like we said before is it a four or five block item.

col2 - The actual line number of each item to hopefully keep the address in order!

Getting the Id, Serial and Name are all straight forward as a starter. I got those using a filter all Col2's with a 1.

Now my next question is it possible to create a formula on Table 2 D2 (zip code) to try to search the col1 & address columns of Table 1 looking for a match with the current row id in Table 2 and the Table 1 col1 but also say a 7 character comprising of only numbers and letters.

Then for telephone numbers from notes1 where the current row id matches col 1 and starts with +1%

Then the address fields somehow order by col2?

Big ask but hope that makes sense.

Thanks again.

Tim

Reply

Answer 3

SGIII

Level 8

35,288 points

Mar 14, 2019 8:18 PM in response to wright1979

Definitely possible to automate or semi-automate the conversion if each "record" in the old table is always in a 4-row by 4-column block with the data in the same position in each bock.

Is that the case, that the old data is in a regular format?

Can you fill out a few more rows of the new table?

SG

Reply

Answer 4

wright1979 Author

Level 1

8 points

Mar 15, 2019 12:59 AM in response to SGIII

Thanks for getting back to me.

In reply to the block of 4’s, it’s not always the case the address row may have 5 rows but there is always 4 columns.

My next thought was to add a few new columns the first with “Row” number.

the next new column would be serial no. and then a column for Id not getting confused with the new id (row no.)

I was then thinking of looping through the row numbers in order and adding the serial number and id by starting at row 1 and seeing if the entry is blank or a number or letter. If it’s a letter add that to the serial column if it’s a number add that to the Id column but then if it’s blank add the previous serial and id.

I’m not sure how to go about that yet but that’s where I’m heading.

I think the row numbers are the key to moving forward.

Thanks,

Tim

Reply

Answer 5

wright1979 Author

Level 1

8 points

Mar 17, 2019 11:36 PM in response to SGIII

Hi, yes I did try that approach thanks but there were so many instances where items where not in the correct position...

What I was trying to ask was now I have added those extra fields to enable me to define what belongs to what item is there a method of building a query say select from from table1 col1 where Id = currentrow and col2 = id and isnumber or contains @ or starts with +1

Many thanks

Tim

Reply

Answer 6

SGIII

Level 8

35,288 points

Mar 18, 2019 1:55 AM in response to wright1979

there were so many instances where items where not in the correct position...

Then it seems like that that what you show in your original post above diverges from your actual data. I tested here based on what you provided and the formulas I suggested work well. Without more details, can only guess what you are working with.

SG

Reply

Answer 7

SGIII

Level 8

35,288 points

Mar 17, 2019 7:30 PM in response to wright1979

I don't really follow what you are asking. Have you tried the OFFSET approach?

SG

Reply