3

I have excel worksheets (csv format) from different clients, where first row of all worksheets contain the column names. However, the order and number of columns in each worksheet can be different. Let me give a minimal example:

Worksheet A

cpu, memory, disk, printer
xxx, xxxxxx, xxxx, xxxxxxx

Worksheet B

network, cpu, memory, mouse 
yyyyyyy, yyy, yyyyyy, yyyyy

So I want worksheets A* and B* such that they only retain the columns that are common in both A and B. So in effect an intersection of A and B with respect to the column names while keeping the previous values.

Worksheet A*

cpu, memory
xxx, xxxxxx

Worksheet B

memory, cpu
yyyyyy, yyy

Any ideas on how to accomplish this in Excel? Any help or pointers will be highly appreciated :)

  • Please note that Super User is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?. – DavidPostill Sep 7 '16 at 16:09
  • @DavidPostill - I have a fair idea how to do it in Python or any other scripting/programming language for that matter, its basically a set intersection problem. The issue is how to do it in Excel. For that I have tried all my googlefu and stackoverflow, most people there seem to suggest VLOOKUP and INDEX functions but I don't see how this can be done using them. So I am stuck at the start. – xkcd Sep 7 '16 at 20:43
1

You need to use a helper row for this to work. I have set up an example the same as the one you posted. In sheet1 I have your first list, sheet2 your second, and sheet3 will contain the desired results.

For sheet1: enter image description here This formula looks in sheet2 for a match. If a match then it displays an index, creating an order.

The formula for column A must be different since it is the first:

enter image description here

Once this is done, we have numbers below the columns where there is a match and an order.

Lastly, we simply need to capture the values with a number so in sheet3 we set up the numbers on the top row and then use this formula to look up the names:

enter image description here

The ones with the error in them, simply mean that the highest number was found so of course discard these ones.

  • Did you notice you don't show the same result the OP is looking for? – fixer1234 Sep 8 '16 at 0:54
  • @fixer1234 How do you figure? I showed how to show the common columns he wants. He can adapt this solution very easily to where he wants the columns. It is shown as an example for him to follow to get the right concept down. – Eric F Sep 9 '16 at 12:35
  • I was focusing on what you show as the result. Typically, if an answer shows results in addition to the formula solution, the results serve to illustrate that the solution works as expected. Usually, the results are displayed as part of a screen shot that shows the source data, and the data closely follows the example in the question. If the answer describes a more general case and uses different source data, the data used is shown in the answer so the results have context. If no source data is shown, the assumption is that what you used matched the example in the question. (cont'd) – fixer1234 Sep 9 '16 at 17:26
  • The question's goal in the example was for the result to be Memory and CPU, which were the two common values. Your result is Network and CPU, which is not what you should get using the question's source data if your formula works. If your formula is correct but you started with different source data, your answer is very confusing without including that data (and a notice that the results are expected to be different and why). – fixer1234 Sep 9 '16 at 17:26
  • @EricF - Yes, this solution is not working on my worksheets. I am getting the columns I don't want. Also, can you please put the formulas in the text rather than images, its not possible to copy/paste them like they are now and increases the chances of mistakes. – xkcd Sep 12 '16 at 13:04

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.