CODEWITHSUNDEEP
stringexcelif-statementcompareformula
Mehmet Akif Güzey
Mehmet Akif Güzey

Reputation: 367

How to compare a string within a string list in Excel

Hi guys, I am trying to write an if statement in Excel but I could not find the right way to make what I need

There are some food names and prices as you can see between I2 and J22. For instance AYÇICEK YAĞI(SUNFLOWER OIL IN ENGLISH) is 4$ per kg. In the left of the sheet, you can see other list. What I need is;

I want to compare all A* columns with Strings between I2:I22 and get the price which is written between J2:J22 then write it to the D* columns.

There are more than 500 rows and I need to do it for all rows.

And there are some headings as u can see in bold font, they should be protected.

Upvotes: 1

Views: 2654

Answers (2)

user4039065
user4039065

Reputation:

You seem to have come up with a formula; now you need a way to dispense it. Your worksheet design does not lend itself to simply filling down a formula. However, with the numbers in column C identifying valid entries that require a formula in columns D and E, a short sub procedure can make quick work of putting the formulas into the correct places.

Sub fillFormula()
    Dim w As Long, vWSs As Variant, vFRMLs As Variant

    vWSs = Array("ogle_aksam_gramaj", "kahvalt" & ChrW(305) & "_gramaj", _
                 "araogun_gramaj")

    For w = LBound(vWSs) To UBound(vWSs)
        With Worksheets(vWSs(w))
            With .Columns(3)   '<~~ going to look at column C for numbers
                With .SpecialCells(xlCellTypeConstants, xlNumbers)
                    .Offset(0, 1).FormulaR1C1 = _
                        "=IFERROR(VLOOKUP(RC1, 'urunler'!C1:C2, 2, FALSE), """")"
                    .Offset(0, 2).FormulaR1C1 = _
                        "=IFERROR(RC4*RC3, """")"
                End With
            End With
        End With
    Next w
End Sub

The IFERROR function has been used to 'wrap' the lookup and mulltiplication formulas. It catches errors and offers alternative results; in this case, zero-length strings that look blank.

The kahvaltı_gramaj worksheet causes problems in VBA due to the unicode character. You might try other methods of cycling through the worksheets.

      gamaji

That binary (macro-enabled) workbook is available from my own public dropbox here.

Upvotes: 2

MDChaara
MDChaara

Reputation: 318

In the workbook you have attached, VLOOKUP will return #N/A when there is no available value.

In Sheet ogle_aksam_gramaj Cell D4 use the following Formula:

=SUMIF($I:$I,$A4,$J:$J)

You can then drag it down and it should be giving you the prices based on the details provided in the same sheet (Range I:J)

The good thing (or bad, depends on you) of sum if is that it will return 0 if there is nothing to sum. in your sheet, the items must be unique in the list, otherwise, it will keep summing every instance. So if AYÇICEK YAĞI is there 2 times, it will be summed twice.

You can use Advanced Filter with (unique values only) to make sure that all the ingredients are accounted for and are mentioned only once.

Thanks.

Upvotes: 0

Related Questions