Vertica lData

[MoldyBread]

Hi, I have data i need to insert into excel, it looks like as follows:

Name: John Citizen
Age: 27
Occupation: Salesman
Name: Bob Citizen
Age: 47
Occupation: Warehousing
Name: James Citizen
Age: 31
Occupation: Real Estate
...etc

and I need to insert a lot into excel in this format:

Name Age Occupation
John Citizen 27 Salesman
Bob Citizen 47 Warehousing
...etc

Any ideas?

[alansidman]

Here is a tutorial on how to create a data entry form that will allow you to enter data and it will appear as requested.

https://www.myexcelonline.com/blog/c...ry-form-excel/

[Roel Jongman]

yup, first idea's make your problem more clear and add a desensitized example.

also answer
- What version of excel you are using that helps us understand what (Advanced) functionality will work for you or not (you can add it in your profile too if you want)
- What are you "inserting into excel? a csv file a txt file that looks like that? (add separate example of input if possible (no real names etc))
- Do you have to do this once or periodicly (like each week or month)

off the bat with little information
have a look at power query, it is a great tool to convert data into desired format.

[Pete_UK]

With your data in column A, starting in A1, and with the headings in C1:E1, you can use this formula in C2:

=SUBSTITUTE(INDEX($A:$A,INT(ROWS($1:1)-1)*3+COLUMNS($C:C)),C$1&": ","")

Copy across into D2:E2, then copy C2:E2 down as far as you need to. The attached file illustrates this.

Hope this helps.

Pete

[MoldyBread]

yup, first idea's make your problem more clear and add a desensitized example.

also answer
- What version of excel you are using that helps us understand what (Advanced) functionality will work for you or not (you can add it in your profile too if you want)
- What are you "inserting into excel? a csv file a txt file that looks like that? (add separate example of input if possible (no real names etc))
- Do you have to do this once or periodicly (like each week or month)

off the bat with little information
have a look at power query, it is a great tool to convert data into desired format.

Excel 2016

It is a raw text file with data filled in vertically, exactly how I typed it out.

I need each row; Name, Age and Occupation, as headers in 3 columns with the data filled in accordingly, basically get rid of all the repeats of Name:, Age: and Occupation:

The Query looks like a good still, but it looks a bit beyond my skill level.

[Roel Jongman]

In excel 2016 they renamed Power Query to "Get and Transform" (to make it sound less scary or advanced :D )and it is default available under the DAta menu.
It is not as difficult as it sounds.. have a look at it.
I cannot show a working solution here.. But i did find one of many tutorial pages on get and transform.
https://datachant.com/2016/01/03/tra...-into-a-table/

this one shows from table but the principle is the same when choosing from txt file instead

[MoldyBread]

With your data in column A, starting in A1, and with the headings in C1:E1, you can use this formula in C2:

=SUBSTITUTE(INDEX($A:$A,INT(ROWS($1:1)-1)*3+COLUMNS($C:C)),C$1&": ","")

Copy across into D2:E2, then copy C2:E2 down as far as you need to. The attached file illustrates this.

Hope this helps.

Pete

This works perfect!

How would I then sort from oldest first, to youngest last?

[Pete_UK]

You should change the formula in D2 slightly by adding:

+0

to the end of the formula, and then copying it down - this will ensure that the age is a numeric value.

Then you can fix the data in those columns (i.e. remove the formulae) by selecting those columns, then click < copy >, then right-click and select Paste Special | Values | OK and then < Esc >.

Then with the data still selected, you can click on Data | Sort and choose Age as the sort column and Largest to smallest as the sort order.

Hope this helps.

Pete