• 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.

Unique and Text Join

kme

Member
1.Bring the item's division name and item's sub-division name to the Answer table through Excel Formula. Bring it from the table given on the excel sheet left side to the Data table.

2.The invoice number should be unique in answer Table . Its Already Have.

3.Item Division Name and Item SUB Division Name have different Division Name and different sub Division Name against some invoice number. Therefore, the Division Name and sub Division Name against the invoice number in the Answer table should come as comma's.

4.Answers Typed in Sample Answers Table. is highlighted in green more than One Item Division's and sub Division's came How it Came in the table. Manually typed Answer Table.

5.Answer Needed In 2nd Sheet
 

Attachments

  • Find (2).xlsx
    14 KB · Views: 3
Won't a simple pivot table do, and be less confusing?:

1722812832551.png

See attached.
Edit post posting: See also table at cell G13.
 

Attachments

  • Chandoo57490Find (2).xlsx
    129.9 KB · Views: 2
Last edited:
There is More Data. That is Hidden . Not In Pivot table . Please Create a formula is more helpfull
 
Thank you for your order/directive.
The current pivot source range is '1.DATA'!$A$2:$F$44, change it to cover all your data, for example '1.DATA'!$A$2:$F$44000

Formula in cell C3:
Code:
=BYROW(B3#,LAMBDA(a,TEXTJOIN(",",TRUE,UNIQUE(FILTER('1.DATA'!E:E,'1.DATA'!B:B=a)))))

Formula in cell D3:
Code:
=BYROW(B3#,LAMBDA(a,TEXTJOIN(",",TRUE,UNIQUE(FILTER('1.DATA'!F:F,'1.DATA'!B:B=a)))))

Why this is not clear and confusing: take this result:
1722857157801.png
Here you might expect that SERVICE CENTRE-MODILE has FLEX as its SUB, and SPARES has LCD, but that's not the case as the pivot table makes clear:
1722857358666.png
which shows the FLEX is SUB to both SERVICE CENTRE-MODILE and SPARES, and LCD isn't SUB to SPARES at all.

Similarly, for:
1722857442523.png
you have 3 Divisions and 5 SUBs; which SUBs belong to which Division? Who knows?
The pivot will tell you:
1722857672144.png
 
Last edited:
Back
Top