locked
how to find nearest value in next column if value doesn't exist in current column linq c# RRS feed

  • Question

  • how to find nearest value in next column if value doesn't exist in current column linq c#
    i am passing param-1 value 12 and the nearest least value is : 10 (Column I)

    Again i am passing param-2 value 32 and the nearest least value is : 30 (Column I)

    if both parameter value exists in same column then it is fine.

    Suppose, if param-1 contains is column "I" and param-2 doesn't exist in column "I",

    then it should immediately move to next column to "J". if both values doesn't exist in "J" then it should immediately move to next column to "K", like that it should search.

    How to do this using LINQ c#

    example values.




    The main intention is, the value should search in every columns form the specific range cell.

    How to do this..?

    Reference Link (Refer) for finding nearest values : https://social.msdn.microsoft.com/Forums/en-US/66d58e88-5008-4707-b954-57b1c27f872b/how-to-find-the-matching-or-nearest-value-from-specified-excel-range-using-c?forum=csharpgeneral

    • Edited by Gani tpt Tuesday, September 22, 2020 3:57 AM Reference Link
    Tuesday, September 22, 2020 2:39 AM

Answers

  • Hi Gani,

    The previous thread was half a year ago. When I looked back at my code at that time, I found that there were many imprecision.

    I spent some time rewriting this code as follows:

            static void Main(string[] args)
            {
                Console.WriteLine("Enter a number:");
                if (int.TryParse(Console.ReadLine(), out int num))
                {
                    DoWork(3, 'F', 6, 'J',num);
                }
                else
                {
                    Console.WriteLine("Not a number.");
                }
            }
            public static double SearchArray(double inValToSearch_, object[,] inArr_)
            {
                if (inArr_ == null || inArr_.Length == 0)
                    return 0;
                List<double> list = new List<double>();
                foreach (var item in inArr_)
                {
                    if (item != null)
                    {
                        list.Add(int.Parse(item.ToString()));
                    }
                }
                return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();
            }
            private static void DoWork(int startRow, char startColumn, int endRow, char endColumn,double num)
            {
                Application xlApp = new Excel.Application();
                Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\test\excel\4.xlsx");
                Worksheet sheet = xlWorkbook.ActiveSheet;
                try
                {
                    object[,] data = sheet.Range[sheet.Cells[startRow, startColumn.ToString()], sheet.Cells[endRow, endColumn.ToString()]].Cells.Value2;
                    double closest = SearchArray(num, data);
                    Console.WriteLine(closest);
    
                    int intStartY = (int)startColumn - 64;
                    int intEndY = (int)endColumn - 64;
                    for (int j = intStartY; j <= intEndY; j++)
                    {
                        for (int i = startRow; i <= endRow; i++) 
                        {
                            if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest)
                            {
                                int row = i;
                                char column = (char)(j + 64);
                                Console.WriteLine(row + " " + column);
                                break;
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
                finally
                {
                    xlWorkbook.Close();
                    xlApp.Quit();
                }
            }

    This code is obviously affected by the previous error:

    if ((sheet.Cells[i, j] as Range).Value.ToString() != null) { IEnumerable<int> Cellvalue = sheet.Cells[i, j].Value.ToString(); ClosestValue = Validation.ClosestValue(Cellvalue, OperLoad);

    }

    sheet.Cells[i,j] is a value, it may be null, if it is null, then null.ToString() will go wrong.

    Please refer to my new code to see if it can give you some new ideas.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 23, 2020 3:02 AM
  • Hi Gani,

    The existing code is compared line by line. You can reverse the order of the two for loops to make them column by column.

    Then use break after finding the first eligible data.

                    for (int j = intStartY; j <= intEndY; j++)
                    {
                        bool state = false;
                        for (int i = startRow; i <= endRow; i++)
                        {
                            if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest)
                            {
                                int row = i;
                                char column = (char)(j + 64);
                                Console.WriteLine(row + " " + column);
                                state = true;
                                break;
                            }
                        }
                        if (state) break;
                    }

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 22, 2020 9:31 AM

All replies

  • Hi Gani,

    Thank you for posting here.

    In the previous code, the code to find the position of the number is this paragraph:

                int intStartY = (int)startIndex[startIndex.Length - 1] - 64;
                int intEndY = (int)endIndex[endIndex.Length - 1] - 64;
    
                for (int i = startX; i <= endX; i++)
                {
                    for (int j = intStartY; j <= intEndY; j++)
                        if ((sheet.Cells[i, j]).Value.ToString() == closest.ToString())
                        {
                            int row = i;
                            // Convert it based on ascii table.
                            char column = (char)(j + 64);
                            Console.WriteLine(row + " " + column);
                        }
                }

    This piece of code uses a loop to compare the closest value found with all the data in the range to find the position. It doesn't care about the column.

    If there are multiple numbers to determine the position, can you call this code repeatedly?

    In this case, there seems to be no relationship between the parameters, but in your description, the column of the first parameter seems to have an effect on the column of the second parameter?

    Did I misunderstand or miss something?

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 22, 2020 6:41 AM
  • I will try to call repeatedly if possible.

    meantime, i am getting the closet value. In that case i want least closest value..

    for example,

    passing the value 25000, then list value, 

    17960
    21300
    22440
    22890
    24500
    25580
    26790
    27100

    so  the nearest least value is ==> 24500. But, i am getting 25580.

    i want least nearest value ==> 24500

    below is the code.

               foreach (var item in data)
                {
                    list.Add(int.Parse(item.ToString()));
                }
                int closest = list.OrderBy(item => Math.Abs(OperLoad - item)).First(); 

    if i use min, can i get 24500 value...?

    Tuesday, September 22, 2020 7:59 AM
  • Hi Gani,

    The strange thing is that I cannot reproduce this problem.

    I have 24500 and 25580, then I input 25000, and always get the correct result.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 22, 2020 9:00 AM
  • Thanks..it's working..

    some times, "H" column values repeating in another column like "K" column.

    In that case, i want to display only one columns by default. below code display all the closest columns.

     char startY1 = char.Parse(startY);
                char endY1 = char.Parse(endY);


                int intStartY = (int)startY1 - 64;
                int intEndY = (int)endY1 - 64;

                char XLcolumn;
                for (int i = startX; i <= endX; i++)
                {
                    for (int j = intStartY; j <= intEndY; j++)
                        if ((sheet.Cells[i, j] as Range).Value.ToString() == closest.ToString())
                        {
                            int row = i;
                            // Convert it based on ascii table.
                            XLcolumn = (char)(j + 64);
                            //Get and add corresponding XL column second
                            ClosedValueResult.Add(Convert.ToString(XLcolumn));
                        }
                }

    the above sample, closest values appearing in two columns. 

    but, it is enough to display only one column ==> "H" truncate the "K" columns.

    shall i skip the loop if finding the after first columns...?

    Tuesday, September 22, 2020 9:18 AM
  • Hi Gani,

    The existing code is compared line by line. You can reverse the order of the two for loops to make them column by column.

    Then use break after finding the first eligible data.

                    for (int j = intStartY; j <= intEndY; j++)
                    {
                        bool state = false;
                        for (int i = startRow; i <= endRow; i++)
                        {
                            if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest)
                            {
                                int row = i;
                                char column = (char)(j + 64);
                                Console.WriteLine(row + " " + column);
                                state = true;
                                break;
                            }
                        }
                        if (state) break;
                    }

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 22, 2020 9:31 AM
  • Thanks for your reply.

    Instead of passing collecting data in range of cell, i would like to check every cell value with parameter using column by column for my easy reference.

    But, i am getting object reference null error when i open the excel sheet. below is my code.

    pls. guide us how to handle this, it means passing single every single value.

    int intStartY = (int)startY1 - 64; int intEndY = (int)endY1 - 64; //Get Specific range all values //object[,] data = sheet.Range[sheet.Cells[LoadHiLightstartX, LoadHiLightstartY], sheet.Cells[LoadHiLightendX, LoadHiLightendY]].Cells.Value2; //char XLcolumn; string ShtName = "MATERIAL"; Excel.Application xlApp1 = new Excel.Application(); Excel.Workbook xlWorkbook1 = xlApp1.Workbooks.Open(MATWB); Excel.Worksheet sheet = xlWorkbook1.Sheets[ShtName]; for (int j = intStartY; j <= intEndY; j++) { for (int i1 = LoadHiLightstartX; i1 <= LoadHiLightendX; i1++) { // First find the closet value if ((sheet.Cells[i, j] as Range).Value.ToString() != null) { IEnumerable<int> Cellvalue = sheet.Cells[i, j].Value.ToString(); //Cellvalue = Convert.ToInt32(Cellvalue); ClosestValue = Validation.ClosestValue(Cellvalue, OperLoad);

    //Second find the closest column

    } } } public static int ClosestValue(IEnumerable<int> Cellvalue, double MatValue) { int ClosestValue = 0; ClosestValue = Cellvalue.Aggregate((r, n) => Math.Abs(r - MatValue) > Math.Abs(n - MatValue) ? n : Math.Abs(r - MatValue) < Math.Abs(n - MatValue) ? r : r < MatValue ? n : r); //if (Cellvalue > MatValue) //{ // ClosestValue = Cellvalue; //} return ClosestValue; }

    First i am getting error in below line.

    if ((sheet.Cells[i, j] as Range).Value.ToString() != null) ==> object null reference error

    second pls. check my condition procedure is correct or not.

    I am trying different logic to arrive the answer. but, i am not able to get exact answer.

    The only thing i want to modify the code, i would like to check every cell value with parameter using column by column for my easy reference.

    pls. help..



    • Edited by Gani tpt Wednesday, September 23, 2020 12:17 AM
    Tuesday, September 22, 2020 2:48 PM
  • Hi Timon,

    Can you reply for the post...? This is priority for my project.

    Wednesday, September 23, 2020 2:26 AM
  • Hi Gani,

    Because the time zone may be different, I may not be able to reply to your question immediately, but once I see your question, I will reply as soon as possible.

    Now I am testing the problem, please wait a minute.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, September 23, 2020 2:37 AM
  • Hi Gani,

    The previous thread was half a year ago. When I looked back at my code at that time, I found that there were many imprecision.

    I spent some time rewriting this code as follows:

            static void Main(string[] args)
            {
                Console.WriteLine("Enter a number:");
                if (int.TryParse(Console.ReadLine(), out int num))
                {
                    DoWork(3, 'F', 6, 'J',num);
                }
                else
                {
                    Console.WriteLine("Not a number.");
                }
            }
            public static double SearchArray(double inValToSearch_, object[,] inArr_)
            {
                if (inArr_ == null || inArr_.Length == 0)
                    return 0;
                List<double> list = new List<double>();
                foreach (var item in inArr_)
                {
                    if (item != null)
                    {
                        list.Add(int.Parse(item.ToString()));
                    }
                }
                return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();
            }
            private static void DoWork(int startRow, char startColumn, int endRow, char endColumn,double num)
            {
                Application xlApp = new Excel.Application();
                Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\test\excel\4.xlsx");
                Worksheet sheet = xlWorkbook.ActiveSheet;
                try
                {
                    object[,] data = sheet.Range[sheet.Cells[startRow, startColumn.ToString()], sheet.Cells[endRow, endColumn.ToString()]].Cells.Value2;
                    double closest = SearchArray(num, data);
                    Console.WriteLine(closest);
    
                    int intStartY = (int)startColumn - 64;
                    int intEndY = (int)endColumn - 64;
                    for (int j = intStartY; j <= intEndY; j++)
                    {
                        for (int i = startRow; i <= endRow; i++) 
                        {
                            if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest)
                            {
                                int row = i;
                                char column = (char)(j + 64);
                                Console.WriteLine(row + " " + column);
                                break;
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
                finally
                {
                    xlWorkbook.Close();
                    xlApp.Quit();
                }
            }

    This code is obviously affected by the previous error:

    if ((sheet.Cells[i, j] as Range).Value.ToString() != null) { IEnumerable<int> Cellvalue = sheet.Cells[i, j].Value.ToString(); ClosestValue = Validation.ClosestValue(Cellvalue, OperLoad);

    }

    sheet.Cells[i,j] is a value, it may be null, if it is null, then null.ToString() will go wrong.

    Please refer to my new code to see if it can give you some new ideas.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 23, 2020 3:02 AM
  • Thanks for your quick reply.

    getting error in below line..

    if (double.Parse(Vendorwsht.Cells[i, j].Value == null ? "0.0" : Vendorwsht.Cells[i, j].Value.ToString()) == closest)

    An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll

    Additional information: Exception from HRESULT: 0x800A03EC

    Note : Instead of passing collecting data in range of cell, i would like to check every cell value with parameter using column by column and cell by cell for my easy reference.

    I want to check inside the loop of the closet value and closest column.

    for (int j = intStartY; j <= intEndY; j++)
     {
             for (int i1 = LoadHiLightstartX; i1 <= LoadHiLightendX; i1++)
                    {

                       //Step 1 : i want to get and check here the closest value using cell by cell comparison                            (cellvalue  and  matrerialValue)

                      //Step 2 :  I want to get corresponding column.

                     //Step 3 --> This is important point.: I am checking some condition based on the value and column, if doesn't match in this loop, i am skipping the loop and continue with next iteration column for checking 

                     }

    }

    • Edited by Gani tpt Wednesday, September 23, 2020 4:06 AM Step 3 --> This is important point.
    Wednesday, September 23, 2020 3:49 AM
  • Hi Gani,

    Do you want to specify the scope, but use the entire drawing as the scope?

    Therefore, is it possible for intStartY and LoadHiLightstartX to be 0?

    If so, it may be the cause of the problem. The cell index of excel starts from 1, and Cells [1,1] is the first cell.

                    for (int j = intStartY; j <= intEndY; j++)
                    {
    
                        for (int i1 = LoadHiLightstartX; i1 <= LoadHiLightendX; i1++)
                        {
                            //Step 1 : i want to get and check here the closest value using cell by cell comparison                            (cellvalue  and  matrerialValue)
    
                            //Step 2 :  I want to get corresponding column.
    
                            //Step 3 --> This is important point.: I am checking some condition based on the value and column, if doesn't match in this loop, i am skipping the loop and continue with next iteration column for checking 
                            if (condition)
                            {
                                break;
                            }
                        }
                    }

    The previous code seems to implement steps 1 and 2.

    For step 3, the loop variable intStartY represents the column. Each time you use break, it means to stop comparing the remaining cells in this column, and then start to judge from the first element of the next column. This seems to meet your requirements. Did I misunderstand something?

    In addition, I seemed to misunderstand what you meant yesterday. There were two identical numbers in the current range of yesterday. How to display only the first one? Yesterday's reply is inappropriate. Now I have modified it.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 23, 2020 7:16 AM
  • Yes. i agree.

    So for it's working fine and matching with my exact logic.

    Wednesday, September 23, 2020 12:28 PM
  • Hi Gani,

    Has your issue been resolved?

    If so, please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by KHURRAM RAHIM Friday, November 27, 2020 10:57 AM
    • Unproposed as answer by KHURRAM RAHIM Friday, November 27, 2020 10:57 AM
    Thursday, September 24, 2020 9:16 AM
  • Yes..Solved..

    Thanks for your assistance friend...

    Friday, September 25, 2020 2:46 AM
  • Hi Timon,

    The same related thread i have raised as below mentioned link.

    I am struggling to get exact result. pls. give some idea.

    Ho do we find closest value column wise instead of row wise C#

    Tuesday, November 24, 2020 11:58 AM
  • This is urgent. pls. help.
    Tuesday, November 24, 2020 1:04 PM
  • Any update.
    Wednesday, November 25, 2020 2:07 AM
  • any update...
    Wednesday, November 25, 2020 4:33 PM
  • Hi Gani,

    Sorry for late reply.

    I searched a bit, but did not find such an API can be used, it seems that we need to write some code manually:

                    List<double> data1 = new List<double>();
                    for (int j = intStartY; j <= intEndY; j++)
                    {
                        for (int i = startRow; i <= endRow; i++)
                        {
                            if (sheet.Cells[i, j].Value != null) 
                            {
                                data1.Add( Convert.ToDouble(sheet.Cells[i, j].Value));
                            }
                        }
                    }

    Best Regards,

    Timon


    "Visual C#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    Thursday, November 26, 2020 5:20 AM
  • Do we get any performance issue if we are using for loop instead object[,] data.

    also i am getting error search array if we are using the above code.

    public static double SearchArray(double inValToSearch_, double inArr_)
            {
                //if (inArr_ == null || inArr_.Length == 0)
                //    return 0;
                List<double> list = new List<double>();
                foreach (var item in inArr_) // Error
                {
                    if (item != null)
                    {
                        list.Add(int.Parse(item.ToString()));
                    }
                }
                return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();
            }

    Thursday, November 26, 2020 9:31 AM
  • Hi Gani,

    Microsoft.Office.Interop.Excel is not an open source package, I don't know how their code is written, so it is difficult to say whether the use of a for loop will cause performance degradation.

    As for the error, it is because the parameter types do not match. Try to modify the SearchArray method:

            public static double SearchArray(double inValToSearch_, List<double> inArr_)
            {
                if (inArr_ == null || inArr_.Count == 0)
                    return 0;
                List<double> list = new List<double>();
                foreach (var item in inArr_)
                {
                        list.Add(int.Parse(item.ToString()));
                }
                return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();
            }

    Best Regards,

    Timon


    "Visual C#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    • Proposed as answer by KHURRAM RAHIM Thursday, November 26, 2020 12:10 PM
    Thursday, November 26, 2020 9:41 AM
  • Thanks for your reply.

    Here one small correction. after listing we should not order by item. it should retain as it is.


    Because, if we are ordering, the column value will change. it will go to the wrong values.

    suppose, my value will be there in first and third column. In this case, i can take the value in the first column itself. so in the array, it will come first and take it for closest value consideration.

    How do i write my code..

    return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();...?

    without ordering we have to take first values in the array list and we should not consider even if the values exists in middle or last of the array (for the same nearest value).

    below screenshot is the details. passing parameter number is "360" or "300"


    if this is works, then my problem solved.

    let me know if you need any more details.



    • Edited by Gani tpt Thursday, November 26, 2020 1:30 PM
    Thursday, November 26, 2020 12:35 PM
  • can you give us the clue..?
    Thursday, November 26, 2020 1:44 PM
  • can you give us the update..
    Thursday, November 26, 2020 3:20 PM
  • This is urgent..pls. help.
    Thursday, November 26, 2020 4:04 PM
  • Hi Gani,

    I can understand your anxious mood, but there is a time difference between us. Sometimes when you post a question, I may still be asleep.

    For this question, I think you may have some misunderstandings, this code:

      var re = list.OrderBy(item => Math.Abs(inValToSearch_ - item));

    It will not make any changes to the original list. Linq is only used to find data. You can think that it has created a new copy of the list to store the sorted data.

    Moreover, in the SearchArray method in my last reply, I also created a new list to store the data.

    This list is necessary when the original parameter is object[,], but if it can be determined that all cell values ​​are numbers , this is enough:

            public static double SearchArray(double inValToSearch_, List<double> inArr_)
            {
                if (inArr_ == null || inArr_.Count == 0)
                    return 0;
                var re = inArr_.OrderBy(item => Math.Abs(inValToSearch_ - item));
                return re.First();
            }

    In order to avoid affecting the results due to some changes to the code by me or you but we don't know, this is my complete code now:

            static void Main(string[] args)
            {
                Console.WriteLine("Enter a number:");
                if (int.TryParse(Console.ReadLine(), out int num))
                {
                    DoWork(3, 'F', 6, 'J', num);
                    Console.WriteLine("Press any key to continue...");
                    Console.ReadLine();
                }
                else
                {
                    Console.WriteLine("Not a number.");
                }
            }
            public static double SearchArray(double inValToSearch_, List<double> inArr_)
            {
                if (inArr_ == null || inArr_.Count == 0)
                    return 0;
                var re = inArr_.OrderBy(item => Math.Abs(inValToSearch_ - item));
                return re.First();
            }
    
    
            private static void DoWork(int startRow, char startColumn, int endRow, char endColumn, double num)
            {
                Application xlApp = new Application();
                Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\test\excel\test4.xlsx");
                Worksheet sheet = xlWorkbook.ActiveSheet;
                try
                {
                    int intStartY = (int)startColumn - 64;
                    int intEndY = (int)endColumn - 64;
    
    
                    List<double> data1 = new List<double>();
                    for (int j = intStartY; j <= intEndY; j++)
                    {
                        for (int i = startRow; i <= endRow; i++)
                        {
                            if (sheet.Cells[i, j].Value != null) 
                            {
                                data1.Add( Convert.ToDouble(sheet.Cells[i, j].Value));
                            }
                        }
                    }
    
                    double closest = SearchArray(num, data1);
                    Console.WriteLine("closest: "+closest);
    
                   
                    for (int j = intStartY; j <= intEndY; j++)
                    {
                        bool state = false;
                        for (int i = startRow; i <= endRow; i++)
                        {
                            Console.WriteLine(sheet.Cells[i, j].Value);
                            if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest)
                            {
                                int row = i;
                                char column = (char)(j + 64);
                                Console.WriteLine(row + " " + column);
                                state = true;
                                break;
                            }
                        }
                        if (state) break;
                    }
                }
    
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
                finally
                {
                    xlWorkbook.Close();
                    xlApp.Quit();
                }
            }
         }

    Best Regards,

    Timon


    "Visual C#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    Friday, November 27, 2020 1:49 AM
  • Thanks.

    The order is coming correct. But, fetching the first closest value is wrong.

    For example. i am passing the value "420". It should search first closest value in between 350 and 600. It should not go beyond that. if nothing is there in the closest then 400 value should come. (below screenshot for your reference)

     public static double SearchArray(double inValToSearch_, List<double> inArr_)
            {
                if (inArr_ == null || inArr_.Count == 0)
                    return 0;
                var re = inArr_.OrderBy(item => Math.Abs(inValToSearch_ - item));
                return re.First();
            }

    I need the very first closest value from the list. if nothing is there in between then find the next value.

    Friday, November 27, 2020 4:23 AM
  • In addition, i will explain my real time requirement.

    below is the real time sample data excel file from client.

    Input parameter : 420

    First i want to find the closest value from the above range which is mainly followed column by column.

    First i will start search "420" in "A" column of every cell.

    For example, (column A)

    420 with 1000

    420 with 2000

    420 with 3000

    420 with 4000

    The above all the case is false. because the closest value difference is so high.

    Next Will start to search Column "B" of every cell.

    420 with 100

    420 with 350 (Here 420 is nearly closest the value and break the loop and note the final value is "350").

    and the final column would be "B".

    like the above i need search code...
    • Edited by Gani tpt Friday, November 27, 2020 10:20 AM like the above i need search code
    Friday, November 27, 2020 7:52 AM
  • Or simply we can take minimum value in between the value "350" and "600". 

    because "420" will come in between of "350" and "600" in the list (without order)

    So we can consider "350" for the final nearest value. (not required here absolute or %).

    ???

    Friday, November 27, 2020 10:56 AM
  • Hi Gani,

    This post is already too big, let us discuss it on the new post published by your Microsoft Q&A, I have posted a new reply there.

    Best Regards,

    Timon


    "Visual C#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.


    Monday, November 30, 2020 7:12 AM
  • Thanks.Done..
    Monday, November 30, 2020 7:52 AM