Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
May 26, 2023

Hlookup or anyother Formula to achieve this

Dear Experts,

                    I have two sheets "Input" & "Output", what I want to Achieve is that in the Output sheet in 1st Column "A", as item 0 has only 1 item so from the "Input" sheet take the first bandNR entry and it's Bandwidth Class info and populate in the output sheet as below, similarly if next Column has item-1 as 2 items , so next two bandNR items shall be populated under this item 1 as shown and so on.

 

Thanks in Advance,

Br,

Anupam

 

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 26, 2023

    anupambit1797 

    To achieve the desired output in the "Output" sheet based on the data in the "Input" sheet, you can use a combination of formulas such as INDEX, MATCH, HLOOKUP, and CONCATENATE. Here's the step-by-step process (File not opened for personal security reasons):

    Assuming "Input" sheet data is in cells A1:AG5, and "Output" sheet starts from cell A1, follow these steps:

    1. In the "Output" sheet, cell A1, enter the following formula:

    =INDEX(Input!$A:$AG, MATCH(ROW()-1, Input!$A$2:$A$5, 0), COLUMN())

    1. Drag the formula across to the right for as many columns as needed to match the maximum number of items in the "Output" sheet (up to item 31 in your example).
    2. In the "Output" sheet, cell A6, enter the following formula:

    =IF($A5<>"", CONCATENATE("bandNR: ", $A5), "")

    1. Drag the formula across to the right to match the number of columns in the "Output" sheet.
    2. In the "Output" sheet, cell A7, enter the following formula:

    =IF($A6="", "", CONCATENATE("ca-BandwidthClassDL-NR: ", INDEX(Input!$A:$AG, MATCH($A6, Input!$A:$A, 0), COLUMN(A7)), " (", HLOOKUP($A6, Input!$A$2:$AG$5, MATCH("ca-BandwidthClassDL-NR: ", Input!$A$1:$AG$1, 0)+ROW(A7)-6, FALSE)), ")"))

    1. Drag the formula across to the right to match the number of columns in the "Output" sheet.
    2. Repeat step 5 for cell A8, replacing "ca-BandwidthClassDL-NR: " with "ca-BandwidthClassUL-NR: " in the formula.
    3. Drag the formula across to the right to match the number of columns in the "Output" sheet.

    Now, the "Output" sheet should populate the desired information based on the data in the "Input" sheet.

    Note: Adjust the ranges (e.g., A1:AG5) in the formulas to match the actual range of your data. Also, adjust the maximum number of items and the starting row (e.g., A2:A5) for the MATCH formulas if your data range differs. The steps given were created with the help of AI.

Resources