Columns Combinations and fetching data in separate form

Dear Excel Experts,

I'm facing a challenging situation on resolving a sheet. I have explained all the required details in the sheet itself. Please help in sorting out the issue.

There are two columns " Current BU Code" & " Debit BU Code ". I need to find which one are the current BU codes, group it and match with Unique Debit BU Code.

After then that combination should get printed in the form with details mentioned in the form.

Request your help.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Ankur,

I have attached the sheet. Hope this will help in finding a solution. Please assist

Originally Posted by fareedexcel

Hi Ankur,

I have attached the sheet. Hope this will help in finding a solution. Please assist

Please updated expected result with specify the criteria.

Hi Ankur,

Please find the expected answer sheet. Please assist

Try

D8

Formula:

Please Login or Register  to view this content.

=

Drag towards and down.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks Ankur. Brilliant !! formula is working fine. But is there any way to automate this. By clicking, the sheet also automatically fetch the correct bu current and debit code in the form sheet, so that we can give printouts instead of typing the codes manually.

Please advise

Ankur, can you please explain on how the IF, SMALL, *, ROW, ROWS function is working. I tried to understand the logic but facing difficulty

Originally Posted by fareedexcel

Thanks Ankur. Brilliant !! formula is working fine. But is there any way to automate this. By clicking, the sheet also automatically fetch the correct bu current and debit code in the form sheet, so that we can give printouts instead of typing the codes manually.

Please advise

Can you explain what is now you are looking for??? My English is not too good

Instead of typing BU CODE and current code in the debit form. Is it possible to do automation with the help of Macros. Macro should identify the correct combinations.

ROW($G$9:$G$43)-ROW($G$8)),ROWS(D$8:D8)) I did not understand the logic.

Also = INDEX(ARRAY,ROW,COLUMN). In this I'm not understanding the row part. Why we are using Small function? and "*" in between two conditions?. Aslo why Row-Row, Rows?

If I understand correctly what you want when you say "Instead of typing BU CODE and current code in the debit form. Is it possible to do automation..." then the following will work without benefit of a macro.
1. A list of unique current BU's, in column P of the templates sheet, using the formula:

Formula:

Please Login or Register  to view this content.

2. Data validation is applied to cell F4 on the expected answer sheet, that retrieves values from that list.
3. I4 on the expected answer sheet is then populated using the formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Dear JetecMc, This is exactly what I'm looking for Brilliant !!. It makes the work so efficient and reduces the time . Thank you so much

Can you please help me with the logic,

ROW($G$9:$G$43)-ROW($G$8)),ROWS(D$8:D8)) I did not understand the logic.

Also = INDEX(ARRAY,ROW,COLUMN). In this I'm not understanding the row part. Why we are using Small function? and "*" in between two conditions?. Aslo why Row-Row, Rows?

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post.
As to formula, one good way to see what it is doing is to use the Evaluate Formula feature on the Formulas tab.
- You'll see that ROW($G$9:$G$43) produces an array of numbers i.e. {9,10,11...43}
- That -ROW($G$8) reduces each element in that array by 8 so that the array becomes {1,2,3...35}
- That ROWS(D$8:D8) produces one in cell D8 and when dragged down to D9 the second argument increments i.e. ROWS(D$8:D9), which produces 2 and so on
- The small function will produce the row number of the smallest row where both (Template!$G$9:$G$43=$I$4) and (Template!$E$9:$E$43=$F$4) are true. Consider the * operator to be like the word 'and' in this case.
Let us know if you have any questions.
I hope that you have a blessed day.

Originally Posted by JeteMc

If I understand correctly what you want when you say "Instead of typing BU CODE and current code in the debit form. Is it possible to do automation..." then the following will work without benefit of a macro.
1. A list of unique current BU's, in column P of the templates sheet, using the formula:

Formula:

Please Login or Register  to view this content.

2. Data validation is applied to cell F4 on the expected answer sheet, that retrieves values from that list.
3. I4 on the expected answer sheet is then populated using the formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Still in the Expected Answer sheet, there is data validation only for the current BU. But the debit BU should also have the data validation part. Because 1 current BU has different debit BU's. If you filter and check the Current BU, for eg:- 19823 it has different Debit BU's (35217,35222,35225,35226,35229). Can you please help on this too.

Fareed now i could undestand what you are looking for you need drop down of unique current BU and respected current BU debit code am i right if yes then you have to follow below steps.

First for current BU as Jet suggested ( refine by me) in sheet Template

P9

Formula:

Please Login or Register  to view this content.

This formula will retrieve current BU unique list which will show as drop down in sheet Expected Answer.

Now the second drop down need all the list which are under current BU Follow below steps for creating them
In sheet Template

Q9

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Create a name manager press Alt->M-N-> New ->>Put name DropDown->> put =OFFSET(Template!$Q$9,,,COUNT(Template!$Q$9:$Q$21)) in section refer to->> ok

now in sheet Expected Answer DEBIT BU CODE press Alt->V->V ->Allo select List->>source press F3 select Dropdown->> Ok

These steps will give you expected results.

Check the attached file

Hope this will help you.

English is not my native language may be some sentence are not correct but must be helpful.

Thanks Ankur. I got the result which I desired. But I have to check in my original sheet and get back to you. After that I will close this thread.

But I have doubt, I tried pressing Alt ->V->V -> Allo Selec list, but it's not working. And more over in the Template sheet, In Column Q, only less Debit BU names are there. But when I checked in the Expected Answer sheet, Debit BU's are coming properly, how it is working?

Originally Posted by fareedexcel

Thanks Ankur.But when I checked in the Expected Answer sheet, Debit BU's are coming properly, how it is working?

This is called magic,,, nothing else. If you read my post properly you will get all the information.

BTW thanks for feedback and happy to help you

Please explain me this concept, I'm not getting proper understanding,

why we are mentioning ROW($G$9:$G$43)-ROW($G$8)) ? why not H9:H43? or I9:I43? . What if I put G9:G55?

Read post no # 15 where all the things has explained

Originally Posted by JeteMc

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post.
As to formula, one good way to see what it is doing is to use the Evaluate Formula feature on the Formulas tab.
- You'll see that ROW($G$9:$G$43) produces an array of numbers i.e. {9,10,11...43}
- That -ROW($G$8) reduces each element in that array by 8 so that the array becomes {1,2,3...35}
- That ROWS(D$8:D8) produces one in cell D8 and when dragged down to D9 the second argument increments i.e. ROWS(D$8:D9), which produces 2 and so on
- The small function will produce the row number of the smallest row where both (Template!$G$9:$G$43=$I$4) and (Template!$E$9:$E$43=$F$4) are true. Consider the * operator to be like the word 'and' in this case.
Let us know if you have any questions.
I hope that you have a blessed day.

What is the reason behind purposely choosing row(g9:g43) ? why not choosing (h9:h43). Is there any reason behind choosing G9:G43? That's what I want to know

You're right the argument of the ROW function could be written H$9:H$43 or even $9:$43. The purpose is to make an array so that the correct cardinal for row (1st, 2nd...35th) gets passed to the INDEX function.
Let us know if you have any questions.

Dear Shukla,

Please help with the offset rule. I tried the same but its not working. How to connect offset rule with Expected Answer sheet. What is the procedure.

If you check in the Expected Answer sheet, Debit BU Code drop down Menu -> Bu Codes are not Unique they are reflecting twice or thrice. But I need it only once. Please help

Originally Posted by fareedexcel

If you check in the Expected Answer sheet, Debit BU Code drop down Menu -> Bu Codes are not Unique they are reflecting twice or thrice. But I need it only once. Please help

Ok try in sheet Template

Q9

Formula:

Please Login or Register  to view this content.

Drag down!!

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Change formula in drop down as below.

Formula:

Please Login or Register  to view this content.

Check the attached file.

Thanks Shukla. Q9 is working fine. As you mentioned, in the Name Manager offset formula is created. But how to link with the Expected Answer sheet Debit BU Code. I tried with Alt->v->v but it is not working. Please help.

Thanks Shukla. Q9 is working fine. As you mentioned, in the Name Manager offset formula is created. But how to link with the Expected Answer sheet Debit BU Code. I tried with Alt->v->v but it is not working. Please help.

Try

Alt->A-> V->V

Sorry I missed it

This is the formula i used in Template sheet in Debit BU

Please Login or Register  to view this content.

Offset Formula :

Please Login or Register  to view this content.

In the Expected Answer sheet, in Chargeback BU code I did the data validation, Im getting the drop down box with Zeros. How to ignore the zeros?

Please help

Looking at the file attached to post #26 I don't see any zeros in either of the drop downs on the 'Expected Answer' sheet (F4 and I4 neither of which is labeled 'Chargeback BU'). I'd suggest that you upload a copy of the file that is showing zeros.
Let us know if you have any questions.

Please find attached file.

1. In the Form Tab, If I select the Current BU Code, In the Debit BU Code Zeros are coming
2. If I change the Current BU Code, Debit BU code should also change immediately.
3. If anyone of both is not related, then I should get an error message.

Where on the Form tab is the Debit BU Code field??? I have changed the code in the drop-down a few times and do not see where these zeros are.

I'm sorry it is Charge back BU Code. Debit BU Code = Charge Back BU Code

I need this 3 criteria to be fulfilled,

1. In the Form Tab, If I select the Current BU Code, In the Charge back BU Code Zeros are coming along with the answers
2. If I change the Current BU Code, Charge back BU code should also change immediately.
3. If anyone of both is not related, then I should get an error message.

Please Help

Nobody has been able to help because you have not explained clearly enough what you want and your sample workbook is not clear, either. You have not answered my question in post #33.

I have replied to you on Post # 34. In Post #35 I mentioned the Criteria. Attached file is in Post #32.

Please find below screenshot for the problem I'm facing in the drop down box showing Zeros,

Try this.
1) Change the Refers to of the named range 'DropDown' so that it reads: =OFFSET(Template!$U$6,0,0,COUNTIFS(Template!$U$6:$U$83,">"&0))
2) Change the source of the data validation assigned to cell H3 so that it reads: =DropDown
Let us know if you have any questions.

I tried but I'm getting an error when trying to link with Offset formula,

Error Message.JPG

Please check my offset formula,

Error2.JPG

I rectified the Error. Now it's working completely fine. I will close the thread after I check in my sheet with required data. If still any error occurs I will look for your help.

Again I got stuck with a problem. I need to select the Current BU Name and it's relative Debit BU Name to get the Staff details displayed in FORM2 Sheet.

I have attached a sample sheet. Please help. Instead of BU code now I need to select with BU Names.. That's It!!

please assist !!

This proposed solution adds another helper column (Y) to reduce the list in column X to unique names.
The array entered formula* that populates column X is:

Formula:

Please Login or Register  to view this content.

The formula that populates column Y is:

Formula:

Please Login or Register  to view this content.

The formula for the 'Refers to' in the named range Unique is:

Formula:

Please Login or Register  to view this content.

The source for the data validation in Form 2:H3 is: =Unique
Let us know if you have any questions.

Thanks. Formula is working absolutely fine. But when selecting the Current BU Name option in Form2, I need to use as an offset formula rather selecting the current BU Name list from Template sheet (w6:w22).

I need as an automation and not to select manually from the Template sheet

I'm getting an error when I tried with this code for choosing deop down in the Current BU NAME in Form 2

Please Login or Register  to view this content.

In the Unique Column, Im getting a blank cell (Green border Cell) please help,

Showing Empty.JPG

Please Help

Please be patient! This forum is manned by volunteers freely and in their own time - it is not a service. Helpers are all over the globe, not just in one time zone.

I believe that the following addresses the concerns of posts #45 and #46.
1) A named range CurrentBUName has been added, with the following 'Refers To' formula:

Formula:

Please Login or Register  to view this content.

2) Data validation for cell H2 on the Form 2 sheet has a source of: =CurrentBUName
It would help in solving the issue raised in post #47 if you would upload a copy of the workbook from which the screenshot was made.
Let us know if you have any questions.

Thanks JeteMc for your support and contribution. Really Appreciate. I'm not closing this thread now, I'm using this code and I have to see the results. Still if I'm facing any problems I will get back.