Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 6th 18, 09:55 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default Formula to copy every other cell value from a single row to a single column,

Hi All,

I have some data in a spread sheet which is on a row, with only one entry every 4 columns.

Now I want to transpose this to a single column but don't want any of the empty cells. Now have tried using "INDIRECT" and this will indeed allow me to select every 4 rows but only horizontally i.e. across the columns. Now I am pretty sure I have to use a combination of formulas to achieve this but which ones I am not to sure as well as how so any help would be greatly appreciated.

Example below.

How it looks...

A B C D E F G H I J K L M..........
1 5 9 7

Using Indirect I get...

A B C D E F G..........
1 5 9 7

Which works as it only gives me the cells I need, however want it as follows;

A
1
5
9
7

Note I only shown 4 entries, but there is many more.

Is there another way of doing this, which I am just not seeing.

Thanks Again.

Andy


  #2   Report Post  
Old June 6th 18, 11:31 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Formula to copy every other cell value from a single row to a single column,

Hi Andy,

Am Wed, 6 Jun 2018 01:55:55 -0700 (PDT) schrieb Andy McNicol:

How it looks...

A B C D E F G H I J K L M..........
1 5 9 7

Using Indirect I get...

A B C D E F G..........
1 5 9 7

Which works as it only gives me the cells I need, however want it as follows;

A
1
5
9
7


in A1:
=INDEX(Sheet1!$1:$1,,ROW()*2-1)
and copy down.


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old June 6th 18, 11:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default Formula to copy every other cell value from a single row to asingle column,

On Wednesday, 6 June 2018 11:31:32 UTC+1, Claus Busch wrote:
Hi Andy,

Am Wed, 6 Jun 2018 01:55:55 -0700 (PDT) schrieb Andy McNicol:

How it looks...

A B C D E F G H I J K L M..........
1 5 9 7

Using Indirect I get...

A B C D E F G..........
1 5 9 7

Which works as it only gives me the cells I need, however want it as follows;

A
1
5
9
7


in A1:
=INDEX(Sheet1!$1:$1,,ROW()*2-1)
and copy down.


Regards
Claus B.
--
Windows10
Office 2016


Thanks Claus,

Your help is much appreciated, now have it all in the same row which is good, however not quite there as getting the following results;

A
0
21
0
23
0
43
0
34

Also should have probably been clearer but want to retain the original data as well so no overwrite in A1. So for testing purposes added the formula into Cell A4, but for some reason it ignores the first 2 values in the row and still returns a 0 between the values.

Sorry to be a pain

Andy
  #4   Report Post  
Old June 6th 18, 12:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Formula to copy every other cell value from a single row to a single column,

Hi Andy,

Am Wed, 6 Jun 2018 03:51:06 -0700 (PDT) schrieb Andy McNicol:

Your help is much appreciated, now have it all in the same row which is good, however not quite there as getting the following results;

A
0
21
0
23
0
43
0
34

Also should have probably been clearer but want to retain the original data as well so no overwrite in A1. So for testing purposes added the formula into Cell A4, but for some reason it ignores the first 2 values in the row and still returns a 0 between the values.


I thought you would have the results in another sheet.
You have data in every other cell. So you have to multiply ROW() with 2.
When you start in row 4 you must change the formula to:
=INDEX($1:$1,,ROW()*2-7)


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Old June 6th 18, 12:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default Formula to copy every other cell value from a single row to asingle column,

On Wednesday, 6 June 2018 12:05:11 UTC+1, Claus Busch wrote:
Hi Andy,

Am Wed, 6 Jun 2018 03:51:06 -0700 (PDT) schrieb Andy McNicol:

Your help is much appreciated, now have it all in the same row which is good, however not quite there as getting the following results;

A
0
21
0
23
0
43
0
34

Also should have probably been clearer but want to retain the original data as well so no overwrite in A1. So for testing purposes added the formula into Cell A4, but for some reason it ignores the first 2 values in the row and still returns a 0 between the values.


I thought you would have the results in another sheet.
You have data in every other cell. So you have to multiply ROW() with 2.
When you start in row 4 you must change the formula to:
=INDEX($1:$1,,ROW()*2-7)


Regards
Claus B.
--
Windows10
Office 2016


Hi Claus,




Thanks again,




And apologies you are right ultimately it will end up on a different sheet, so probably should have done that, but wanted to view the results on the same sheet for test purposes so my fault.




Anyway our help is greatly appreciated as was pulling my hair out as I know I have done this before, but for the life of me cannot remember what and why I used it. So many spread sheets I have been through over the years.




Just one thing now and then I am sorted, I am still; getting a 0 between each of numbers./ Thus how do I get rid of this so I only get the cells with the actual values in it. Note that on the original sheet there will be an entry every 5th cell on the row.




Regards,




Andy


  #6   Report Post  
Old June 6th 18, 12:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Formula to copy every other cell value from a single row to a single column,

Hi Andy,

Am Wed, 6 Jun 2018 04:30:48 -0700 (PDT) schrieb Andy McNicol:

Just one thing now and then I am sorted, I am still; getting a 0 between each of numbers./ Thus how do I get rid of this so I only get the cells with the actual values in it. Note that on the original sheet there will be an entry every 5th cell on the row.


where are your values? In every third cell? Then you must multiply with
3.
Your table in your question was in a mess and I couldn't see in which
columns your values are.


Regards
Claus B.
--
Windows10
Office 2016
  #7   Report Post  
Old June 6th 18, 12:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Formula to copy every other cell value from a single row to a single column,

Hi Andy,

Am Wed, 6 Jun 2018 04:30:48 -0700 (PDT) schrieb Andy McNicol:

Just one thing now and then I am sorted, I am still; getting a 0 between each of numbers./ Thus how do I get rid of this so I only get the cells with the actual values in it. Note that on the original sheet there will be an entry every 5th cell on the row.


have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgc5oNtEN1ZcSxHN4NQ


Regards
Claus B.
--
Windows10
Office 2016
  #8   Report Post  
Old June 6th 18, 02:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default Formula to copy every other cell value from a single row to asingle column,

On Wednesday, 6 June 2018 12:51:06 UTC+1, Claus Busch wrote:
Hi Andy,

Am Wed, 6 Jun 2018 04:30:48 -0700 (PDT) schrieb Andy McNicol:

Just one thing now and then I am sorted, I am still; getting a 0 between each of numbers./ Thus how do I get rid of this so I only get the cells with the actual values in it. Note that on the original sheet there will be an entry every 5th cell on the row.


have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgc5oNtEN1ZcSxHN4NQ


Regards
Claus B.
--
Windows10
Office 2016


Claus,

You are a superstar, thank you very much all is good now. That link explained it all and let me know what I had to do visually.

Thanks again,

Hopefully I can return the favour in the future.

Andy


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
can I copy a number of cells in a column to a single cell ina row Bev[_2_] Excel Discussion (Misc queries) 2 July 3rd 08 12:11 PM
I would like to copy UNIQUE data from a column in a SINGLE CELL Yogi Excel Discussion (Misc queries) 2 July 14th 07 07:20 AM
From single cell variables to a single column serie noyau New Users to Excel 1 December 22nd 06 06:43 AM
Copy column range of "single word" cells with spaces to a single c nastech Excel Discussion (Misc queries) 3 February 15th 06 05:04 PM
Copy column of cells to a single cell? nastech Excel Discussion (Misc queries) 7 February 15th 06 12:39 PM


All times are GMT +1. The time now is 12:03 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017