CODEWITHSUNDEEP
excel
gchq
gchq

Reputation: 1759

Merging rows to import

When we import data for new customers they generally export to Excel with all the required information for each record on one row

Scratching my head with this one as each record is spread over several lines, but there is a discernible pattern

e.g.

Name

Address

Town, State Zip

Blank

Blank

Without having to copy and paste each entry how could I convert that to

Name | Address | Town, State Zip |

Sorry if this seems a daft question, but it is Monday :-)

Deleting the blank lines is not an issue once the other part is resolved

Thank you

Upvotes: 0

Views: 27

Answers (2)

nutsch
nutsch

Reputation: 5962

With formulas, if your first name is in cell A1, put the following formula in cells B1, C1 and D1:

=IF(AND($A6<>"",COUNTA($A4:$A5)=0),OFFSET($A1,COLUMN()-2,0),"")

Copy down and you have your list. You'll have to edit the last two rows for cleanup.

Upvotes: 1

nbayly
nbayly

Reputation: 2167

So quick and dirty way to do this is to grab your whole table, copy it as text into a blank Word document. Then using "Find and Replace" do a Replace All for "^p^p^p" and replace with "SpecialSpecial". This looks for 3 consecutive Paragraph Marks and replaces it with a unique marker ("SpecialSpecial" though could be anything as long as it's not replicated in your data).

Then you search for "^p" and replace with "^t". This replaces all other Paragraph Marks with tabs. Last Replace all the unique markers you created with a Paragraph Mark ("^p"). Then when you copy your data back into Excel it will be ordered how you wanted it. Cheers,

Upvotes: 1

Related Questions