-4

I have the following table in excel

[Base Date](https://i.sstatic.net/bqBWw1Ur.png)

I have tried a number of formulas to get what I want such as:

=IFERROR(INDEX($H$1:$Z$1,MATCH(TRUE,INDEX($H2:$Z2>0,0),0)),"")

This returns the number 6431 which is correct, but what I need is to calculate a table which would look like something as follows:

[finished data set]

(https://i.sstatic.net/f55WLAP6.png)

There could be zero, one or multiple values in each row, all adding up to the total in Column D.

Improve this question
1
  • 1
    You can unpivot the columns in power query and then filter out blanks in the value section and remove the other unnecessary columns. Commented May 29, 2024 at 13:08

2 Answers 2

Reset to default
3

Highlight your table and under "data" in the ribbon select "From Table/Range" selecting that your data has headers.

Remove the columns you don't need.

Once the Power Query window opens, highlight the columns you want on their side, then in the "Transform" tab of the ribbon select "Unpivot Columns". This will make the headers their own column, and the values another column. From here, it should remove the null values, but if it doesn't you can filter those out at this step.

If you want them summarized for duplicates, select Group By otherwise you can Close and Load and it should give you what you need.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi Mark, In English, what i want it to do is: there is a value in R2 (380) so i want it to put the corresponding column header in R1 and the corresponding row header from C2 in another cell It would then look like the following AJ 6431 380 AJ 6407 471 and so on
Yes, that is what my instructions explained. In power query, that is a simple function. Highlight your columns H:Z and "unpivot" then one column will be the headers, the following column will be the values, and the rest of the columns (A:G) will be left alone, besides creating rows to accommodate the new data structure. Remove whatever columns you don't want, maybe filter out null, and should be all set.
Glad it was helpful, what's especially handy of tables & power query is as more data is introduced to the table (rows, not columns) you can simply refresh the query and it will do the rest. If this answered your question please mark it as so :)
That's the goal! Say you get additional columns every week as well, it may be easier to select which columns you DON'T want unpivoted, so it will unpivot the rest instead of explicitly defining which you want to unpivot (so when more come in, power query won't necessarily know it has to do something to them).
@user25309931 when dealing with large datasets and unpivoting or stacking them it is best to use Power Query as already mentioned here(+1 to poster), no offence to any solution but using function like TEXTJOIN() or etc may get you FALSE Positives or wrong output too risky we dont suggest in those using because text functions has limitations. and when you are using a TEXTJOIN() to combine multiple cells into one array it will eventually cross the per cell limit of 32767 characters which should have been added by the other user as caveat!
|
1

This formula uses TEXTJOIN, TEXTSPLIT and TOROW functions

=TEXTSPLIT(TEXTJOIN("|",TRUE,TOROW(IF(H2:P4>0,TEXT($C$1,"mm/dd/yy")&"\"&C2:C4&"\"&$H$1:$P$1&"\"&H2:P4,1/0),3)),"\","|")

enter image description here

If the date format not correct change "mm/dd"yy" to "dd/mm/yyyy"

Improve this answer

1 Comment

I’ll be trying this one. I am trying to automate a lot of processes to save manual entry x

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.