Hey everyone! I’m struggling with a spreadsheet problem. I’ve got this big file with two tabs. Each tab has two columns: one for item codes and another for prices. The first tab has about 10,000 rows, while the second has around 6,500.
What I’m trying to do is find all the item codes that show up in both tabs. Then I want to keep only those matching entries and delete everything else. So if an item code appears in both tabs, I want to see its details from both tabs side by side.
For example, if item 9365 is in both tabs but with different prices (like 54 in one tab and 59 in the other), I want to keep both of those entries and remove all the other non-matching items.
Is there an easy way to do this in Excel (2003 or 2007) or Google Sheets? I’m not great with formulas, so any help would be awesome. Thanks!
hey, u tried pivot tables? they work well. create one for each tab, set item codes as rows, filter rows with empty colums. it takes a bit of tweaking but saves formulas. good luck!
For this task, I’d recommend using a combination of COUNTIF and filtering. Start by adding a new column in each tab with a formula like =COUNTIF(Sheet2!A:A,A2)>0 for the first tab and =COUNTIF(Sheet1!A:A,A2)>0 for the second. This will return TRUE for matching item codes. Then, filter each tab to show only TRUE results. Copy these filtered results to a new sheet, placing data from the first tab on the left and the second on the right. You can then use a simple formula to align the matching entries side by side. This method is straightforward and doesn’t require advanced Excel skills. It’s also easily adaptable to Google Sheets if you prefer working there.
I’ve tackled a similar challenge before, and what worked best for me was to use VLOOKUP as a starting point. I began by adding a new column on the first tab where I used a formula like =VLOOKUP(A2, Sheet2!A:B, 1, FALSE) to determine if each item code also appeared in the second tab. After filtering out the non-matching rows, I copied the remaining entries to a new worksheet. I then performed a similar check on the second tab and finally combined the matching data. This approach involves some manual steps but is straightforward and effective for a one-time task. Advanced techniques like INDEX-MATCH could be used for larger datasets, but the VLOOKUP method served my needs well.