copy data from multiple collums into one colum ignoring blanks

[luis6777]

good afternoon, im looking for a formula that will let me copy values from several columns into one ignoring any blanks attached a sample

will need to copy from colum B,E.H,K,N,K to one column S


thanks in advance

[Jeckford]

Try the below:

Please Login or Register  to view this content.

[luis6777]

HI
Thank you for the reply, this only copy all the names into the same cell

Try the below:

Please Login or Register  to view this content.

[Jeckford]

HI
Thank you for the reply, this only copy all the names into the same cell

Sorry, just re-read you post, didn't see the from part. Same sort of formula, just a couple more cells included.

Please Login or Register  to view this content.

If this is not what your chasing, can you please tell me what you want your result to be? I think you might be better off with a macro.

[luis6777]

hi
what im looking for is copy all data from column B,E.H,K,N,K into the column S but i need to have one name per cell.
Im happy to use a macro for it

Thanks

[Jeckford]

I think I know what your chasing now, sorry. You just want a list of names, correct? So S7 will = Smith Craig and S8 will = Panchal Ramanben, correct?

[samba_ravi]

see the attached file

[Jeckford]

test.xlsm

Try the below or attached

Please Login or Register  to view this content.

[Jeckford]

Not sure if the attachment is working for me.

[Jeckford]

Ignore the first attachment. Use this one. The macro should have read "Do While b < 18" not "Do While b < 17"

Please Login or Register  to view this content.

Hope this helps

[luis6777]

Great thanks

[luis6777]

Sorry just need help with one more thing, i will need this macro to work from one spreadsheet to other, Cabn you please highlite in the macro what related to cells and sheets so i can adjust to my needs
Thanks

[Jeckford]

a = the row the first name
b = the column the first name appears
x = the row you want the list to start from
cells(x, 19) = the 19 is the column of the list (S)
b < 18 = the 18 is the column you want to search up to (it will search up to column 17 btm (Q)
sheets("Sheet2").select = the sheet you want the macro to run in (if your view the correct sheet, this line can be deleted from the macro)

also put the below in the macro after the sheets("Sheet2").select, so that it gives you a fresh list each time the macro is run, for if the names change. Change the S:S to whichever the list is in.

Please Login or Register  to view this content.

Hope this is what you were chasing

[samba_ravi]

is post no 7 not working?

[Jeckford]

This might be easier to read.

Please Login or Register  to view this content.

[luis6777]

is post no 7 not working?

it does but i do think that will be better with a macro, i do have loads of data in the spreadsheet and macros are not so have in the spreadsheet (i think) correc me if i m wrong please

[luis6777]

How should i do if for example the columns are in shets11 and i want to copy the names to shet 22

This might be easier to read.

Please Login or Register  to view this content.

[Jeckford]

How should i do if for example the columns are in shets11 and i want to copy the names to shet 22

change Cells(x, 19) = Cells(a, b) to sheets("sheet22").cells(x, 19) = sheets("sheet11").cells(a, b)

Please Login or Register  to view this content.

You will need to change the x = 5 tand the 19 part of sheets("sheet22").cells(x, 19). If you want the list to be in the first row and column, x = 1 and 19 needs to be changed to 1.

Hope this works.

[Jeckford]

And change

Columns("S:S").Select
Selection.ClearContents

to

sheets("sheet22").select
Columns("S:S").Select
Selection.ClearContents
sheets("sheet11").select

change the S:S to A:A if the list is in the first column

[luis6777]

HI

I decided to use the original sheet but i had to add some columns and the destination column had to be diferent, unfortunatly im not able to make the macro work anymore. could i have a hand please

thanks

Ignore the first attachment. Use this one. The macro should have read "Do While b < 18" not "Do While b < 17"

Please Login or Register  to view this content.

Hope this helps

[luis6777]

now with attachement

[Jeckford]

HI

I decided to use the original sheet but i had to add some columns and the destination column had to be diferent, unfortunatly im not able to make the macro work anymore. could i have a hand please

thanks

Try the attachment now. I've made a couple of changes to the macro as follow:

b = 2
to
b = 3

Cause the names appear in the 3rd column now and not the 2nd

b < 18
to
b < 24

Cause the names now finish in the 23rd column now (W)

cells(x, 19)
to
cells(x, 29)

Cause the list now appears in the 29th column (AC)

I also change the below. I never explained this, but this is the frequency the names appear. In your new sheet, your names are spaced an extra column apart from each other, that is why it is not + 4, instead of + 3.

I changed the b = b + 3 to b = b + 4

Hope this explains it a bit better.

Please Login or Register  to view this content.

[luis6777]

hi this is great, how can i add on this macro that i want to take names only till a specific row in each column

Try the attachment now. I've made a couple of changes to the macro as follow:

b = 2
to
b = 3

Cause the names appear in the 3rd column now and not the 2nd

b < 18
to
b < 24

Cause the names now finish in the 23rd column now (W)

cells(x, 19)
to
cells(x, 29)

Cause the list now appears in the 29th column (AC)

I also change the below. I never explained this, but this is the frequency the names appear. In your new sheet, your names are spaced an extra column apart from each other, that is why it is not + 4, instead of + 3.

I changed the b = b + 3 to b = b + 4

Hope this explains it a bit better.

Please Login or Register  to view this content.

[Jeckford]

hi this is great, how can i add on this macro that i want to take names only till a specific row in each column

Are you able to explain a little bit more please. Not 100% sure in what your asking. I have an idea of what your asking, but just want to make sure.

[luis6777]

i only would like to copy from raw 5 to raw 31 instead of copu all column

Are you able to explain a little bit more please. Not 100% sure in what your asking. I have an idea of what your asking, but just want to make sure.

[Jeckford]

Before I post anymore, tell me if this attachment works. I've re-written the macro so that it should always work itself out*. If you look in the attachment, you will see I've added a row and that there is text in red font now in that row. The macro will only read cells below the cell(s) containing the text "Name" (In red font, (you can change the font color if you want) and your list will be displayed below the cell containing the text "List of Names". You can add new columns, space columns further apart, insert new rows and the macro will still run, as long as you put another cell called "Name" above the new names entered. The macro now searches the sheet for the cells containing "Name" and "List of Names"

Test it out, remove one of the cells called "Name" and a heap of the names will not appear in the list.
Or try adding a new column and adding some names in that column. But make sure "Name" is in one of the cells above the list of names.

Note 1. Name and List of Names are case sensitive and space sensitive, if you enter "LISt of Names" or "List of Names ", the macro will not run or will come up with error.
Note 2. The cells called "Name" all have to be on the same row. "List of Names" can be wherever.
Note 3. All data must stay on sheet

Let me know if I need to explain anything better. Basically, you should now not ever have to edit the macro.

[Jeckford]

The macro will work on any sheet as long as you add the "Name"s and the "List of Names"

[luis6777]

this is great but it still copy names that are in fron of the cells that says holidays, i do not want to incluid that names

Before I post anymore, tell me if this attachment works. I've re-written the macro so that it should always work itself out*. If you look in the attachment, you will see I've added a row and that there is text in red font now in that row. The macro will only read cells below the cell(s) containing the text "Name" (In red font, (you can change the font color if you want) and your list will be displayed below the cell containing the text "List of Names". You can add new columns, space columns further apart, insert new rows and the macro will still run, as long as you put another cell called "Name" above the new names entered. The macro now searches the sheet for the cells containing "Name" and "List of Names"

Test it out, remove one of the cells called "Name" and a heap of the names will not appear in the list.
Or try adding a new column and adding some names in that column. But make sure "Name" is in one of the cells above the list of names.

Note 1. Name and List of Names are case sensitive and space sensitive, if you enter "LISt of Names" or "List of Names ", the macro will not run or will come up with error.
Note 2. The cells called "Name" all have to be on the same row. "List of Names" can be wherever.
Note 3. All data must stay on sheet

Let me know if I need to explain anything better. Basically, you should now not ever have to edit the macro.

[Jeckford]

this is great but it still copy names that are in fron of the cells that says holidays, i do not want to incluid that names

So there should be two names that are missed out? See this attachment. The macro will run up to a row that contains the text "Holiday" (row 35 in the case of your sheet), if "Holiday" is not found, it will run for 500 rows. Again "Holiday" is case and space sensitive.

[Jeckford]

So if you enter "Holiday" into cell A1 (or D1 or G1), no names will be displayed. If you enter "Holiday" into cell A7 (or F7 or J7), 6 names will be displayed.

[Jeckford]

So there should be two names that are missed out? See this attachment. The macro will run up to a row that contains the text "Holiday" (row 35 in the case of your sheet), if "Holiday" is not found, it will run for 500 rows. Again "Holiday" is case and space sensitive.

Opps, use this attachment. Left something in the macro that shouldn't have been there.

[Jeckford]

If you want to manually choose what row the macro runs up to, change

do while a < holidayrow
to
do while a < 64

(64 is just an example, choose what ever row you want the macro to run to. Note that the macro will run to 63 not 64, it has a +1)

Please Login or Register  to view this content.

[luis6777]

good afternoon
i need help again in something similaqr with the above
i have dat ain column a and column b and i need to copy to column c but i will need toignore the blanks, my apolagies but the above is to complex for me to edit

Thaks

[Jeckford]

Is it a new spread sheet your working on, or is this an add on to the above? If it is an add on, are you able to attach the worksheet so I can see what has changed and what needs to be done.

If it's a new spread sheet and you just want to copy columns a and b to c, excluding blanks, see code below:

Please Login or Register  to view this content.

[luis6777]

thank you for your reply
this is something new, the macro above is great, could you just help me by making some adjustments on it

The colums that i need to take data from are column K row 5 and column R row 5 and will need to go to column K row 154, your help would be much apreciated

Is it a new spread sheet your working on, or is this an add on to the above? If it is an add on, are you able to attach the worksheet so I can see what has changed and what needs to be done.

If it's a new spread sheet and you just want to copy columns a and b to c, excluding blanks, see code below:

Please Login or Register  to view this content.

[Jeckford]

I've change the macro so that it might be easier for you to amend. I've put comments in the macro so that you can see whats going on.

Please Login or Register  to view this content.

If there is anything that is confusing, just let me know and I'll try to explain it.

[Jeckford]

Note that this is reading every bit of data between columns K and R (excluding blanks), so if there is random data in column N, it will appear in your list of data.