Extracting data from a copy/paste

**FortuneSyn** · 01-25-2010, 12:03 PM

(Excel 2007- portuguese(brazil) version)
Attachment included!

Hello!

I am making an excel sheet that will graph many monthly results based on data extracted from a company's custom software. My objective is to minimize manual input, so I am trying to find ways for the excel sheet to read info that is copy pasted and appropriately place it in a graphable format.

I attached an example of a data extract that would basically be copy/pasted to the excel sheet.

My current idea is using VLIST and MATCH to find the column number of the VLIST needed. However, I don't know how to identify the data of the extract by date because the year and month are divided between cells, and some cells are merged, etc.

How would you make it so that the data that is copy pasted into a sheet falls nicely into the table in the excel sheet? Sorry for the long question, I hope some of you guys can help me out.

Kind Regards.

**NBVC** · 01-25-2010, 12:16 PM

That file is not recognizable... are you sure it is an excel file?

**FortuneSyn** · 01-25-2010, 12:27 PM

I have uploaded the file again on this post and on the original post, in 2007 with macro format. See if this is better for you.

**FortuneSyn** · 01-25-2010, 12:41 PM

Originally Posted by FortuneSyn

(Excel 2007- portuguese(brazil) version)

My current idea is using VLIST and MATCH to find the column number of the VLIST needed. However, I don't know how to identify the data of the extract by date because the year and month are divided between cells, and some cells are merged, etc.

Kind Regards.

Sorry, I didn't mean VLIST, I meant VLOOKUP. However, a better solution is always welcome!

**NBVC** · 01-25-2010, 12:44 PM

Try in B17:

Please Login or Register  to view this content.

or depending on your version....

Please Login or Register  to view this content.

Portuguese translations for functions:

INDEX = ÍNDICE
MATCH = CORRESP
TEXT = TEXTO

**FortuneSyn** · 01-25-2010, 01:42 PM

Thanks for the quick response NBVC. Unfortunately I received an error "#N/D" on all the cells that I dragged it to.

I think the error is in the "yyyy" and "mm-mmmm" translations, so i tested them out to see what they should be.

=TEXTO(B$16;"åååå") gives me "2008"
=TEXTO(C$16;"mm-mmmm") gives me "10-oktober"

"10-Oktober" is october in Danish, because I bought this computer in Denmark! That is why the dates from the extract were in text and not number format, because it wasn't recognizing the language.

- What do you recommend to solve this? Change computer language? Is that possible?
- If I send this excel sheet to another computer (which I have to), would the formula still work?

**NBVC** · 01-25-2010, 01:47 PM

Try uploading the attached and see if the formulas translate automatically....

**FortuneSyn** · 01-25-2010, 02:02 PM

I have uploaded the excel file you uploaded into this post.

when i opened your excel file the formula looked like this:

=ÍNDICE($B$7:$N$12;CORRESP($A17;$A$7:$A$12;0);CORRESP(TEXTO(B$16;"yyyy");$B$4:$N$4;0)+CORRESP(TEXTO(B$16;"mm-mmmm");$B$5:$N$5;0)-1)

So it looks like it did translate everything except the "yyyy" for me.

**NBVC** · 01-25-2010, 02:11 PM

I only used this function because you said you were copy/pasting the data.. and I see that 2009 and 2010 were showing as text... if you can change those so that they are numbers, the formula would not need the TEXT() function....

**FortuneSyn** · 01-25-2010, 02:29 PM

Unfortunately I cannot change those into numbers or date instead of text. If I start to request to the people that will update this worksheet to manually change the years in text into date, there's bound to be human error.

**NBVC** · 01-25-2010, 02:36 PM

How about the attached?

**FortuneSyn** · 01-25-2010, 02:42 PM

In the excel sheet you just sent, the only cells that work are F17-F22 (jan/10 with Name1-6). The others are showing "#N/D"

edit: maybe it's because the "01-janeiro" "02-fevereiro" etc dates copy/pasted into excel are written down as 2010, and F16 is the only date with 2010 in it.

**NBVC** · 01-25-2010, 02:52 PM

It's hard to test when not using the same Language version... I made a couple of changes to "anglicize" the document, eg. change 09-Setembro to 09-September and change 2009 to 2008 in B4 and I got results in B17 down...

**FortuneSyn** · 01-25-2010, 03:00 PM

This is very strange. The excel file you sent me that i said didn't work actually works 100%! However, in cell B4 the year is written like this " '2009 " with a ' in there before the year. however I ran some tests and when i extract the data, the little ' doesnt come along with it.

When I delete the ' , the cells stop working.

**NBVC** · 01-25-2010, 03:04 PM

Do the copy paste over it again.. I was testing things and converted that to a number... but realized you probably needed it to stay text, so adding the ' in front converts it to text.. and probably messes things up for you.

Just try to copy/paste as you originally did...

**FortuneSyn** · 01-25-2010, 03:10 PM

IT WORKS!!!!!

Thankyou so much for your time on this afternoon! This formula will help me power through the other 50 graphs I have to automatize.

**NBVC** · 01-25-2010, 03:12 PM

Great. I am happy it worked...

If you are satisfied with the solution please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**FortuneSyn** · 01-25-2010, 03:13 PM

double post

**FortuneSyn** · 01-25-2010, 03:18 PM

For some reason the company LAN settings block me from being able to give you reputation. When I get home I'll make sure to do it.

**NBVC** · 01-25-2010, 03:20 PM

Originally Posted by FortuneSyn

For some reason the company LAN settings block me from being able to give you reputation. When I get home I'll make sure to do it.

Much appreciated

Extracting data from a copy/paste

LinkBack

Thread Tools

Rate This Thread

Display

Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Re: Extracting data from a copy/paste

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions