Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
May 25, 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...
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum 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.