Formula needed for cell date tranfer 1

[bbenaway]

Have numbers on sheet 1, column A that I would like to bring over to sheet 2, column A. Easy enough, but I would also like to bring over other date (names,etc.) that corresponds with those numbers from sheet 1, column B, C, D, etc to sheet 2 column B, C, D.

 

[SkipVought]

hi,

Check out the VLOOKUP function. A typical formula might look like...

on Sheet2 in paste this formula into B2
[tt]
B2: =VLOOKUP(A2,Sheet1!A,Column(),False)
[/tt]
and COPY this cell, then PASTE into the other cells in that row B thru ALL ROWS of data on Sheet2

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Wicca]

Hmmm I have a similar problem but not as easily solved
I have the following text data in one cell and wish to break it into individual cells

8710-2000 Bbri Water Salaries 741.34 2% 741.34 2% 36,488 36,488
8710-2010 Bbri Water Travel/Accomm 164.27 21% 164.27 21% 800 800

I used left(CELL,9) and extracted the 8710-2000 that was straight forward
but I also want to extract other data and put into other cells

Using the top row as an example
8710-2000 Bbri Water Salaries 741.34 2% 741.34 2% 36,488 36,488

I would like to extract into seperate Cells
8710-2000.......36,488.........741.34

I could cut and paste but being around 1200 rows and needing to be done monthly . . . I would prefer a simple formula if there is such a thing.


I have looked throught the FAQs but cannot find the answer . . .

Thanks in advance.


With respect
Wicca

Believe in yourself, you are worth the investment.

 

[Wicca]

A few more lines from the spreadsheet that may help with your thoughts . . Im totally baffled but then I am not great at this anyway

8700-3655 Bell Operating Bore Sites 4,819.54 32% 4,819.54 32% 15,000 15,000
8700-3660 Bell Operating Reserviors 482.24 40% 482.24 40% 1,200 1,200
8700-3665 Bell Sampling and Testing 1,256.12 63% 1,256.12 63% 2,000 2,000
8700-3670 Bell Meter Read and Testing 175.11 12% 175.11 12% 1,500 1,500

Im sure there is a way to find the last "space" and go from there to get the last set of digits but the ones in the middle of the string baffle me as there are a varying amount of spaces . . . if that makes sense.

Many thanks

With respect
Wicca

Believe in yourself, you are worth the investment.

 

[xlbo]

for a one off:
Data>Text to columns>Set delimeter as 'space'


I'm guessing that you are loading this data into the spreadsheet from some form of text file (can't imagine that the data would be physically entered into excel this way). If that is the case and you need to di this regularly then you can set up an import specification. See this Link for more details

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question