• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Modify the formula for fetching unique names from all Sheets of the file

H

Hany ali

Active Member
Hello my Dear ,I Want Your Help to Modify the formula which in Column B From Total Sheet ,to Get all unique Names with not get names by + Sign like (Ahmed Samy+Oleksandr Puga+Samir Nageh Adam) ,But these names must be separated so that they appear like the rest of the names, as one name in each cell without any + Sign , If there is any name, from this Group, that was brought before, then it is not brought
Code: 
=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,Sheet1!C$3:C$164,Sheet2!C$3:C$164,Sheet3!C$3:C$164,Sheet4!C$3:C$164,Sheet5!C$3:C$164,Sheet6!C$3:C$164)&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")
Finally, please modify the equation of column C to fetch the amounts of each name from all Sheets, provided that the amounts of the names associated with the + sign are divided by the number of names between this sign.
Code: 
=SUMPRODUCT(SUMIF(INDIRECT("'"&$O$2:$O$7&"'!$c$2:$c$164"),B2,INDIRECT("'"&$O$2:$O$7&"'!$b$2:$b$164")))
 

Attachments

  • Screenshot 2023-11-10 211243.png
    Screenshot 2023-11-10 211243.png
    57.8 KB · Views: 8
  • unique.xlsb
    59.7 KB · Views: 8
Here is a Power Query Solution

Code: 
Source = Table.Combine({Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"BOOKED BY"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each if Text.Contains([BOOKED BY],"+") then null else [BOOKED BY]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BOOKED BY"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Duplicates", each ([Custom] <> null and [Custom] <> "BOOKED BY"))
in
    #"Filtered Rows1"
 

Attachments

  • PQ Unique Names.xlsx
    19.9 KB · Views: 1
Thank you very much for your kind response, but is there a solution with regular equations? There are also columns of sums, and I really do not understand anything about them Power Query.
 
I do not see in your request in Post 1 any mention of the other columns.
1. Which columns do you wish to keep and do you wish that they are accumulated (summed) for multiple instances.
2. If not, what data should be included?
3. How will the numbers which are shared by those having a "+" be accounted for?
4. Also, it will make a difference as to which version of Excel you are using. Please advise as that is very important if you expect a formula solution.
I will post in my next response with how you can use the Mcode I supply. I await your response before attempting another solution. As far as a formula solution, you will need to wait for someone else.
 
Last edited:
Thank you very much for your kind response ,The column must also be compiled from all Sheets of the file B and D
3. How will the numbers which are shared by those having a "+" be accounted for?
These Amount will be Sharing For All Names Between "+" ,For Examble .if I Have like (Ahmed Samy+Oleksandr Puga+Samir Nageh Adam)
I divide their amount by 3 .. etc
I use excel 365 , and in Home 2016
 
I know that you prefer a formula solution. I don't have one for you. If you are willing to go down the Power Query Street then here is the Mcode.

Code: 
let
    Source = Table.Combine({Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Promoted Headers", {{" Total Sales ", each Text.AfterDelimiter(_, "$"), type text}, {"4%", each Text.AfterDelimiter(_, "$"), type text}, {"1%", each Text.AfterDelimiter(_, "$"), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{" Total Sales ", Int64.Type}, {"4%", Int64.Type}, {"1%", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.Count(Text.Split([BOOKED BY], "+"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sales", each if[#" Total Sales "]>0 then [#" Total Sales "]/[Custom] else [#" Total Sales "]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "4%Split", each if [#"4%"]<>0 then [#"4%"]/[Custom] else [#"4%"]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "1%split", each if [#"1%"]<>0 then [#"1%"]/[Custom] else [#"1%"]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom3", {{"BOOKED BY", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "BOOKED BY"),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"BOOKED BY", "Sales", "4%Split", "1%split"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Sales", type number}, {"4%Split", type number}, {"1%split", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"BOOKED BY"}, {{"Total Sales", each List.Sum([Sales]), type nullable number}, {"Total 4%", each List.Sum([#"4%Split"]), type nullable number}, {"Total 1%", each List.Sum([#"1%split"]), type nullable number}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([BOOKED BY] <> "BOOKED BY"))
in
    #"Filtered Rows1"

Here are some instructions on how to use PQ

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

and the file is attached.
 

Attachments

  • PQ Unique Names.xlsx
    21.6 KB · Views: 2
Thank you very much for your kind response..But I am still waiting for solutions and responses from someone who finds this formula
 
@Hany ali

1] Please be note the forum rule: one post one question,

2] Being for the formula to extract unique names from all Sheets of the file, the revised Excel 2019 formula will be:

In B2, formula copied down:

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("</b><b>",1,Sheet1:Sheet6!C$3:C$164),"+","</b><b>")&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")

1699975839798.png
 
A truly genius and excellent solution. Thank you very much. As for the equation of the total amounts starting from the column C, is it required to open a new post for it?
 

AlanSidman​

All this time I have been trying and I can only achieve what I have achieved and I fail and see for yourself
 

Attachments

  • Example.xlsb
    62.8 KB · Views: 1
  • Screenshot 2023-11-23 001441.png
    Screenshot 2023-11-23 001441.png
    97.3 KB · Views: 1
this video should help. You need to bring each of the tables into PQ in order to append them which you have not done in your example workbook.

 
You must log in or register to reply here.
Back
Top