Extracting Unique Data from Repeating Parameters in Excel: Dynamic Approach Needed

[nilaakash]

I have an excel dataset with three columns:

Column 1: Parameter-a values (repeating)
Column 2: Parameter-b values (repeating)
Column 3: Data values for different parameters corresponding to each unique combination of values in columns 1 and 2.

I want to extract individual data from the third column based on unique combinations of values from the first two columns, and I want this process to be dynamic, meaning it should work for any dataset with similar structure.

Please find the attached file where dataset and my answer are explained better way.

ThanksDataset.jpg

[dflak]

I know that this is a small data set, but I (and probably most people) am not going to take the time to create a workbook with your data. Including a sample workbook (see banner at the top of the page) will greatly enhance you chances that somebody will be willing to work your issues. I can do this with a combination of a pivot table and some vb code.

[nilaakash]

Hi dflak, thank you for your input. I'm new to Excel and seeking guidance from experienced individuals in this forum. I prefer to avoid using VBA code since I'm not very familiar with it.

[kvsrinivasamurthy]

Try. In E4.

=LET(a,A2:A12,b,B2:B12,c,C2:C12,abu,UNIQUE(a&","&b),rst,TEXTSPLIT(TEXTJOIN("_",TRUE,BYROW(abu,LAMBDA(ro,TEXTJOIN(",",TRUE,IF(a&","&b=ro,c,""))))),",","_",FALSE,1,""),cab,TEXTSPLIT(TEXTJOIN("_",TRUE,abu),",","_",FALSE,1,""),rr,HSTACK(cab,rst),rr)

[nilaakash]

Thank you kvsrinivasamurthy for your outstanding code.

Your code works well for small datasets, but I have a much larger one with 30 parameter columns and 20 data columns (experiment results).

For each unique combination of parameter values, I have several sets of experimental results (4 to 8) in a single column (column C). I need to calculate summary statistics for these results, depending on the situation.

Average: I can easily calculate the average using the AVERAGEIFS function dynamically.

Median and Standard Deviation: I'm struggling to find a dynamic way to calculate these for each unique parameter combination.

My goal is to efficiently analyze the data in column C. I can't simply get the data vertically because it would cause errors. That's why the original code used a transpose approach.

Please get the attached file. If possible, could you please provide your input there.

Nilaakash_1.xlsx

[nilaakash]

Try. In E4.

=LET(a,A2:A12,b,B2:B12,c,C2:C12,abu,UNIQUE(a&","&b),rst,TEXTSPLIT(TEXTJOIN("_",TRUE,BYROW(abu,LAMBDA(ro,TEXTJOIN(",",TRUE,IF(a&","&b=ro,c,""))))),",","_",FALSE,1,""),cab,TEXTSPLIT(TEXTJOIN("_",TRUE,abu),",","_",FALSE,1,""),rr,HSTACK(cab,rst),rr)

Thank you 🙏

[wk9128]

Hi nilaakash , Currently your column C does not appear to be duplicated. Is there a possibility of duplication? Would You accept using EXCEL VBA CODE ?

[nilaakash]

Hello wk9128, I appreciate your comment. The data in column C is experimental and is less likely to repeat. I prefer to avoid using VBA code since I'm not very familiar with it.

[AliGW]

What's the nature of the REAL data? If it's numeric, why did you show sample data that's text?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[nilaakash]

Hello AliGW, my apologies if I've made any mistakes. I'm a new member of this forum and still learning the rules. Initially, I wasn't sure if attaching an Excel file was permitted or not. I aimed to present my dataset clearly without including specific numbers. Nonetheless, I've attached the dataset in an Excel file for reference.

[AliGW]

Please read and act upon post #7.

[AliGW]

There is no file attached - please try again.

[nilaakash]

There is no file attached - please try again.

Here it is

Nilaakash_1.xlsx

[AliGW]

But do you still need assistance? Or is it moot in the light of post #12?

[nilaakash]

But do you still need assistance? Or is it moot in the light of post #12?

Apologies for any confusion. I truly appreciate your assistance. I've attached the file (Nilaakash_1.xlsx) for kvsrinivasamurthy, who provided me with a helpful code. I've requested another solution from them, and if possible, I'd like to incorporate their input into my file. Thank you for your support.

[AliGW]

What are your expected results in N2 and O2 (values)?

[AliGW]

Maybe try:

=BYROW(H2:K5,LAMBDA(r,AVERAGE(r)))

=BYROW(H2:K5,LAMBDA(r,MEDIAN(r)))

=BYROW(H2:K5,LAMBDA(r,STDEV.P(r)))

for kvsrinivasamurthy, who provided me with a helpful code.

Anyone can offer help.

[nilaakash]

Thank you sincerely for providing your code. Your dynamic solution is truly appreciated. However, I've encountered an issue with the H column in my own code—it's not dynamic, requiring manual dragging to populate values completely. I'm seeking assistance to make this part of the code dynamic as well. Any help would be greatly valued.

[AliGW]

For this to work properly, KM's formula will need to be tweaked:

=LET(a,A2:A13,b,B2:B13,c,C2:C13,abu,UNIQUE(a&","&b),
rst,--TEXTSPLIT(TEXTJOIN("_",TRUE,BYROW(abu,LAMBDA(ro,TEXTJOIN(",",TRUE,IF(a&","&b=ro,c,""))))),",","_",FALSE,1,0),
cab,TEXTSPLIT(TEXTJOIN("_",TRUE,abu),",","_",FALSE,1,""),
rr,HSTACK(cab,IF(rst=0,"",rst)),
rr)

[nilaakash]

Thank you, AliGW, for sharing your code. I'll need some time to grasp it fully since I'm not an Excel expert. If I encounter any difficulties or uncertainties, would it be possible for me to reach out to you for assistance?

[Czeslaw]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Parameter-a", "Parameter-b"}, {{"Count", each _, type table [#"Parameter-a"=text, #"Parameter-b"=text, Data=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Data]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17", "Custom.18", "Custom.19", "Custom.20", "Custom.21", "Custom.22", "Custom.23", "Custom.24", "Custom.25", "Custom.26", "Custom.27", "Custom.28", "Custom.29", "Custom.30", "Custom.31", "Custom.32", "Custom.33", "Custom.34", "Custom.35", "Custom.36", "Custom.37", "Custom.38", "Custom.39", "Custom.40", "Custom.41", "Custom.42", "Custom.43", "Custom.44", "Custom.45", "Custom.46", "Custom.47", "Custom.48", "Custom.49", "Custom.50", "Custom.51", "Custom.52", "Custom.53", "Custom.54", "Custom.55", "Custom.56", "Custom.57", "Custom.58", "Custom.59", "Custom.60"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Count"})
in
    #"Removed Columns"

[AliGW]

However, I've encountered an issue with the H column in my own code

Fixed in post #19.

EDIT: You don't need your formula - it's all done in KM's tweaked formula that I provided in post #19. These are formulae, not code - code is VBA.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AliGW]

Just post again to this thread if you need help with this FORMULA (not code).

For any new queries, start a new thread.

For now, if that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[kvsrinivasamurthy]

Try. single formula in D4. Last 3 columns are average, median, sd.

=LET(a,A2:A12,b,B2:B12,c,C2:C12,abu,UNIQUE(a&","&b),rst,VALUE(TEXTSPLIT(TEXTJOIN("_",TRUE,BYROW(abu,LAMBDA(ro,TEXTJOIN(",",TRUE,IF(a&","&b=ro,c,""))))),",","_",FALSE,1,0)),cab,TEXTSPLIT(TEXTJOIN("_",TRUE,abu),",","_",FALSE,1,0),avg,BYROW(rst,LAMBDA(ra,AVERAGE(IF(ra=0,"",ra)))),mdn,BYROW(rst,LAMBDA(rb,MEDIAN(IF(rb=0,"",rb)))),sd,BYROW(rst,LAMBDA(rc,IFERROR(STDEV(IF(rc=0,"",rc)),0))),rr,HSTACK(cab,rst,avg,mdn,sd),rr)

[nilaakash]

Thank you again, kvsrinivasamurthy, for your assistance with the code. I was hoping for a slightly different approach, where the codes are separated into three different cells for clarity. I appreciate your willingness to incorporate my input into your file. If possible, could you please integrate your code accordingly? Your responsiveness is greatly valued.


extracting unique data_nil.xlsx

[kvsrinivasamurthy]

In D13

=LET(a,A2:A12,b,B2:B12,c,C2:C12,abu,UNIQUE(a&","&b),cab,TEXTSPLIT(TEXTJOIN("_",TRUE,abu),",","_",FALSE,1,0),cab)

In G13

=LET(a,A2:A12,b,B2:B12,c,C2:C12,abu,UNIQUE(a&","&b),rst,VALUE(TEXTSPLIT(TEXTJOIN("_",TRUE,BYROW(abu,LAMBDA(ro,TEXTJOIN(",",TRUE,IF(a&","&b=ro,c,""))))),",","_",FALSE,1,0)),IF(rst=0,"",rst))

In L13

=LET(a,A2:A12,b,B2:B12,c,C2:C12,abu,UNIQUE(a&","&b),rst,VALUE(TEXTSPLIT(TEXTJOIN("_",TRUE,BYROW(abu,LAMBDA(ro,TEXTJOIN(",",TRUE,IF(a&","&b=ro,c,""))))),",","_",FALSE,1,0)),avg,BYROW(rst,LAMBDA(ra,AVERAGE(IF(ra=0,"",ra)))),mdn,BYROW(rst,LAMBDA(rb,MEDIAN(IF(rb=0,"",rb)))),sd,BYROW(rst,LAMBDA(rc,IFERROR(STDEV(IF(rc=0,"",rc)),0))),rr,HSTACK(avg,mdn,sd),rr)

[nilaakash]

Thank you, kvsrinivasamurthy, for providing such efficient codes. Your kindness and expertise are truly appreciated.
Best regards.


[AliGW]

Once again, these are FORMULAE, not code or codes.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[nilaakash]

Thank you, AliGW. You're correct; these are indeed formulas, not codes. I attempted to add reputation by clicking on the * button, but unfortunately, it didn't work. I'm unsure why.

[AliGW]

Didn't work? Did you get an error message? There should be no problam for you as a brand new member.

[nilaakash]

Please take a look at the attached file, where it indicates, "You must spread some Reputation around before giving it to kvsrinivasamurthy again." I may be wrong.

star.jpg

[AliGW]

That means that you have already awarded that member reputation. However, I have not had any from you, so it should work for me.

Please stop quoting unnecessarily.

[AliGW]

You seem to have sorted it now - thank you.