1

I have two columns in Excel of the same type of variable called Reduced Store Numbers and Original Store Numbers. Reduced Store Numbers has fewer rows than `Original Store Numbers.

I want Original Store Numbers, Sunday, Monday, and Tuesday reduced to the same rows as Reduced Store Numbers. Is there a function to do that?

Here's a sample of the dataset:

Reduce Store Numbers    Original Store Numbers  Sunday  Monday  Tuesday
1                       1                       0       19      20
2                       2                       0       0       12
3                       3                       0       300     45
4                       4                       0       5000    100
7                       5                       0       25      150
                        7                       0       30      25

Desired dataset:

Reduce Store Numbers    Original Store Numbers  Sunday  Monday  Tuesday
1                       1                       0       19      20
2                       2                       0       0       12
3                       3                       0       300     45
4                       4                       0       5000    100
7                       7                       0       30      25

I have not tried anything and do not know where to start. The original dataset is a larger size than the one posted here.

2
  • Very likely you will need Macro /VBA to solve it.
    – jcbermu
    Aug 14, 2015 at 14:36
  • I could go by every line and delete the rows, then cut/paste, but there are too many. I am not sure how the Macro could distinguish which rows I want deleted, cut, and pasted back. Aug 14, 2015 at 15:20

2 Answers 2

1

Maybe the quickest solution is addition of a helper column, like in the picture below:

enter image description here

The formula for this column is: =IFERROR(IF(MATCH(C2,A:A,0)>0,C2),""). That will display "original store number" if it's included in the "reduce store number" column, else it'll be empty.

After it you can filter for numbers in the new column and copy data to a new sheet.

3
  • After filtering B, I copied C:F into a new tab. I took A and pasted right next to the copied columns. Aug 17, 2015 at 17:03
  • @ScottDavis: Column B already contains the desired values, what is the advantage of copying only C:F and column A separately instead of B:F? Aug 17, 2015 at 18:48
  • After filtering B for taking out zeros, B and C have the same store numbers. For example, C will not have store 5. Aug 18, 2015 at 14:05
0

Cut the last four columns in second worksheet, then vlookup or index match only column in first worksheet with that of second worksheet.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .