Adapt CONCATENATE to skip rows

[Celex005]

Hi,

I'd like to know how adapt the CONCATENATE function to skip rows when copying the formula down a column. For example I am merging rows 3 & 4 using =CONCATENATE(Sheet1!$C3:$AN3,Sheet1!$C4:$AN4) onto Sheet 2 row 3 but it would be very helpful if I could then merge rows 5 & 6 from sheet 1 on row 4 of sheet 2 automatically.

When I copy the formula down currently sheet 2 cell c4 reads =CONCATENATE(Sheet1!$C4:$AN4,Sheet1!$C5:$AN5)

Any thoughts?

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Celex005]

Thanks for the tip zbor.

[zbor]

Look here lot of snb solutions for CONCENATE http://snb-vba.eu/VBA_UserDefinedFunctions_en.html

[Celex005]

Looks like a useful link but I'm not quite ready for visual basic yet and thought the inclusion of the ROW function might work.

I have experimented with ROW($A$1)+various combinations with the original formula to no avail.

Any suggestions?

[FDibbins]

not tested, but maybe combine row() with mod() to only pull in even (or odd) rows?

[FDibbins]

ok give this a shot...

=CONCATENATE(INDIRECT("Sheet1!$C"&IF(MOD(ROW(A3),2)<>0,ROW(A3),ROW(A3)+1)&":$AN"&IF(MOD(ROW(A3),2)<>0,ROW(A3),ROW(A3)+1)),Sheet1!$C5:$AN5)

if it works for the 1st part, modify the 2nd part the same way

[Celex005]

Thanks FDibbins, I haven't used the mod function before but it sounds like it has got potential. Do you know how it could be be incorporated into the =CONCATENATE(Sheet1!$C3:$AN3,Sheet1!$C4:$AN4) formula?

[Pete_UK]

Here's another approach - put this formula in C3 of Sheet2:

=SUMIF(Sheet1!$B$3:$B$12,$B3&"*",Sheet1!C$3:C$12)

If you don't want to see a zero returned, then you can apply conditional formatting to the cell such that if C3=0 then use a white foreground colour. Then you can copy the formula down and across to get your other values, as in the attached file.

Hope this helps.

Pete

[Celex005]

That's great, thanks to both of you.

I continued to have a play around and managed to come up with a cheat way of avoiding the zero values by adding ascending values to the previously blank cells in column A alongside the table & combining them with FDibbins formula before seeing Pete's post.

Thanks again!