Solved

# Excel Formula Help

Posted on 2014-03-25
331 Views
Hello Experts,

Attached is the Excel file that I need help with. In the sheet labeled "Changes Jan to Feb" I used a formula to compare the data in "BOM 1" and "BOM 2" to tell me which parts have changed. If possible, I would like a formula for "Changed Only 1" that will fill in columns A-E with only the parts that have changed in "Changes Jan to Feb," but not the ones that read "N/A" or "NEW PART!"

Thanks,
Drew
Example.xls
0

LVL 3

Assisted Solution

englanddg earned 166 total points
I think I see what you are trying to do.

I'd say, either use VBA for this, or create a hidden sheet that consists of your "Changes Jan to Feb" info, then have a viewable sheet that uses a vlookup to return only those items which have changed.
0

LVL 23

Accepted Solution

NBVC earned 168 total points
Perhaps try this formula method.

In the Jan to Feb sheet add a helper formula in column F.

At F2 enter:

=IF(D2="CHANGE!",COUNT(F\$1:F1)+1,"")

copied down.  This cumulatively counts the items with Change in column D

Then your formula in another sheet to get the results would be:

=IFERROR(INDEX('Changes Jan to Feb'!A:A,MATCH(ROWS(\$A\$2:\$A2),'Changes Jan to Feb'!\$F:\$F,0)),"")

copied down and across as far as necessary.
0

LVL 5

Assisted Solution

indrajitmahajan earned 166 total points
0

Author Closing Comment

Thanks all, you guys all helped!
0

## Featured Post

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.