Is it possible to conditionally paste info from one chart to another?

Question

Level 1

8 points

Is it possible to conditionally paste info from one chart to another?

Hi all,

I've got a large spread sheet of products and I'm trying to find if there's a way to paste info from one chart to another based on conditions.

For example, below you'll see I've got an ID column and a Main Image column. The ID column is the 'master column' (meaning it's on my master sheet, the sheet I'm pasting new content into). The Main Image column is the column which I'm pasting from another spread sheet.

My goal:

To get the number in the ID to match the number in the Main Image column (for example, 4 = 4_large.jpg, 5 = 5_large.jpg).
If there is no main image file with the same number as the ID column (for example, ID 2 doesn't have a .jpg file that matches it), skip it/leave cell blank.

Based on my research, I haven't been able to find a way to align these rows based on their numbers. Does anyone know of a method to accomplish what I'm trying to accomplish?

Many thanks in advance for your help and insight.

Best,

Andrew

Posted on Jan 20, 2019 9:13 AM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Jan 20, 2019 12:21 PM in response to androojones

Hi Andrew,

Assuming your example accurately shows the pattern of the jpeg file names (integer followed by a single underscore character followed by the file name and .jpg extension, the following should give you the results you want.

You will need two tables: one to paste the Main Image list to (Table 2) , the other (Table 1) to collect the image names matching the ID list in column A.

Table 2 contains the formula below, entered in A2 and filled to the end of column A.

A2: IFERROR(LEFT(B2,FIND("_",B2,start-pos)−1),"")

The formula returns the characters before the underscore in the text in column B. The result is a text value.

The values copied from 'another table' are pasted to this one by selecting (one click) the top left cell to contain the newly pasted values (here, B2), then pressing command-B. The values in column A are calculated by the formula in that column.

The values in Column A of Table 1 are entered values (in this case the first two were entered, the rest constructed by selecting those two and dragging the fill handle (small yellow circle that appears on the bottom of A3 when the pointer is placed near there) down the column.

Column A cells must be formatted as text to match the format in column A of Table 2.

Column B od Table 1 contains the formula below, entered in B2 and filled down.

B2: IFERROR(VLOOKUP(A2,Table 2::A:B,2,FALSE),"")

The FALSE argument sets VLOOKUP to accept only an exact match. If one is not found, VLOOKUP returns a 'can't find' error message, which is trapped by IFERROR. IFERROR then returns a null string ( "" ), making the cell appear empty.

New image names should be pasted to empty rows of Table 2 (below the rows already containing data).

Regards,

Barry

Reply

Answer 2

androojones Author

Level 1

8 points

Jan 20, 2019 3:56 PM in response to Barry

Hi Barry,

I was just about to respond with follow-up questions, but I overlooked a crucial step in this: "Column A cells must be formatted as text to match the format in column A of Table 2."

Before highlighting Table 1, Row B > Format > Cell > Data Format = Text, I was only getting blank cells. After completing this step, I was able to get the info that I wanted:

You're a wizard, Barry. Thanks again for your help on this.

Best,

Andrew

Reply