Extracting information between two sheets and place it in 3 columns

**Tricky99** · 11-15-2014, 08:30 AM

Hi everyone,

I have a certain data in excel sheet which i want to extract into three columns.

here is the raw data. It's a comma separated values file (csv).

This is what it should be the final result, after formatting the raw data:

on sheet "Formatted_2000", you can see that there is an information from column A to column U.

The information column A to column L was easy one. I have done "text-to-columns" to separate them by ID, Last Name, First Name, Birth Date and so on..

But you can see at the "RAW_2000" sheet what is left - unsorted raw data.

On sheet "Formatted_2000" I've made columns M to U and put the information manually.

It took me a lot time to do it, so I am searching another way to sort it like this.

I'd be very grateful if you can suggest an options on how to proceed further.

**xladept** · 11-15-2014, 08:54 PM

Hi Tricky,

I may have something for you tomorrow

**Tricky99** · 11-16-2014, 03:09 PM

Hi xladept,

I'd appreciate that!

Tricky

**xladept** · 11-16-2014, 04:37 PM

Hi Tricky,

Welcome to the Forum! To run this you need to add a sheet called "Sheet1" etc.

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

**Tricky99** · 11-16-2014, 04:50 PM

Hey,

Thank you! Nailed it ! I've checked a few rows with the already Formatted 2000 data and it seems everything is okay !

**xladept** · 11-16-2014, 05:53 PM

Your welcome!

But, because of the input - you'll still need to do some manual adjustments

**Tricky99** · 11-16-2014, 05:58 PM

Yes, of course.

There are cases with 3 or even 4 universities, so I have to check the degrees and search the people without education info

**xladept** · 11-16-2014, 06:32 PM

Yeah - I've got 4 on my resume.

Have Fun!

**Tricky99** · 11-16-2014, 06:50 PM

I haven't got time to see it in depth. Although, I'll try to make it on Python, think it's much easier than VB.

Also, I have to create a small database for the grades, to pick the greatest and the least of them.

Thanks again.

**Tricky99** · 11-17-2014, 04:08 PM

Hey,

It seems something went wrong with the data again.

It didn't extract the information in the right sequence.

It extracts only one of the line between ##EDUCATION### and ###END###

Also it doesn't read the "Birth Date" properly.

Can you help?

**xladept** · 11-17-2014, 08:04 PM

Hi Tricky,

Try this:

Please Login or Register  to view this content.

**Tricky99** · 11-18-2014, 02:51 AM

Hey,

I'll try the second code as well. Thanks again.

Although, I've managed to fix it with the first one.

**JBeaucaire** · 11-18-2014, 04:14 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Tricky99** · 11-18-2014, 08:00 AM

It does indeed, but I have one more case:

For some of the cases between ###EDUCATION and ###END### I have several universities with different degrees and their gradations is like this:

If we have, for instance, 3 universities with 3 degrees: The last one before ###END### row is the first university attended by the person. The first one after ###EDUCATION###, if we have more than 1 line, is the last university and probably the highest degree.

###EDUCATION###
PhD, Yale University(3) - HIGHEST
MA, University of Pennsylvania (2)
BA, Delaware State College (1)
###END###

Is it possible to rearrange the code somehow to take the first one (1) and If there are more than 2 lines, to take the 3rd or the 4th, because it will be the highest. I have to fullfil the Highest degree .

You can see where column "Highest degree" is placed on the Sample.xlsx file.

I think the idea is to make a database with the degrees, but since there is a gradation in the raw data(education), i think it would be better to take it directly from it.

I know I am starting to get annoying, but it would minimze my workings to only do the researches with the missing data.

Would appreciate that !

Cheers,
Ivan

**xladept** · 11-18-2014, 02:00 PM

Hi Tricky,

Have you tried my last version - I think it takes care of the first and last degree!

**Tricky99** · 11-18-2014, 02:10 PM

Hi xladept,

Yes, I've tried it and now the universities are missing

**xladept** · 11-18-2014, 03:44 PM

Tricky.xlsx- this is what I've got

**Tricky99** · 11-18-2014, 04:30 PM

Yes, it works with this example. I tried with the same structured raw data and the same sheets, but for another state - IDAHO(ID) - it didn't work.

Let me try it one more time and I will post the result here.

EDIT 1: It is working. I've checked 2-3 people with different education and it does take the greatest degree.

Amazing script!

Thank you one more time. Case solved !

**xladept** · 11-18-2014, 04:37 PM

You're welcome and thanks for the rep!

Extracting information between two sheets and place it in 3 columns

LinkBack

Thread Tools

Rate This Thread

Display

Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Re: Extracting information between two sheets and place it in 3 columns

Thread Information

Users Browsing this Thread

Similar Threads

Extracting Row Information if Value appears in Columns to List under Column Heading

Database - Extracting particular information from 3 columns on one row in a long list.

Excel 2007 : How to grab information from other sheets and place it in the main sheet

Extracting information from several sheets

[SOLVED] Extracting cell information from various sheets to a master list

Bookmarks

Bookmarks

Posting Permissions