Copy & Paste Values Based on IF Statement

[Montoro22]

Hello,

I need a fairly simple macro made, but am getting stuck up. I've attached the file for reference.

First, on the 'Post ID' tab:
- I need to copy and paste all values that are not 0 from columns B, C, D into column A
- The inputs in columns B, C, D are formulas, which is why i need to paste as values
- For the current attached example, it should return 540 post IDs
- I'm leaving a running formula in columns B, C, D so that I don't have to drag down any further and the post IDs will automatically update on this tab as we get the data

Second, on the 'Combined' tab:
- I want to copy and paste as values all the post IDs from column A in the tab 'Post ID' (row 2 as far down as the post list goes based on the above statement)
- This will re-paste the full updated list from the tab 'Post ID' into the 'Combined' tab starting in cell A4

I hope this gives enough detail for what I need. In general, I just need the macro to give me the full list of post IDs for me to run vlookups off of. I've dragged down the formulas to retrieve the post IDs in columns B, C, D as far down as I anticipate (which is why you see 0's going down to row 5000 on the 'Post ID' tab). But there is the small chance I would need to drag it down further.

Thanks in advance!

[Arkadi]

for the Post ID tab, you say you want BCD into A if the value is not 0.... do you mean when column C is not 0 or B or D? (or when none of the columns are 0),
and do you want columns BCD combined into A? if so, any separator between them? (I also don't understand how you get to 540 since there are only 527 rows with numbers in them, and many of those are 0 across all columns...

EDIT I believe I just got it... You want all the values from B C or D that are not 0 copied over to column A individually?

[Richard Buttrey]

Hi Arkadi

I struggled to understand that too. In the end I realised the 540 is
245 non zeros in column B, +
77 non zeros in column C +
218 non zeros in column D.

[Montoro22]

Sorry for the confusion, that is correct. I want to select all rows in column B that are not 0, copy and paste as values over to column A, then do the same for column C & D. They do not need a separator in column A, just the full list that pulls all post ID's that are not 0.

So it would copy rows 2 through 246 in column B (since the 0's start after that) and paste as values in A2. Then it would copy rows 2 through 78 in column C, and paste as values starting in A247 (since that is where the last copy and pasted values from column B ended. Then it would continue this process for the data in column D.

After the complete post ID list (inclusive of all post IDs from columns B, C, D) is in column A, that will then be transported over to the other tab as noted in my initial post.

Thanks again!

[Arkadi]

Lets see if this works for you?

Please Login or Register  to view this content.

[Montoro22]

One other thing I forgot to mention. Could the macro replace whatever pre-existing data is in the columns when pasting as values? Since it will be a running total of all the post IDs, it should always be larger every time we run the macro with the updated list.

[Arkadi]

Lol montoro, yes I put in some code that clears the old data first (that's the optional section), then saw your addition

[Arkadi]

OOOPS! I had made a small mistake... it didn't clear old data properly... should be fixed now, I edited my post.

[Montoro22]

Works like a charm! Thank you so much!

Was just about to mention the first post ID being duplicated, but it looks like the edit fixed that.

[Arkadi]

You are very welcome Glad it seems to do what you want.
Once you are sure it works well, please remember to mark the thread as solved? Thanks!