How to populate the column 3 under this condition??

[raghuprabhu]

Hi All,

I have a small problem.

In sheet2 of the attached file, I have in column1 have group, column2 has items, column3 also has the same items, some of them missing.

When I select an item in the form from the dropdown list, I want the comment to be populated as shown. If the item selected from column2 is from group1 and is not in column3 then I want the comments column to be as shown.

I need some help to achieve this.

Thanks

Raghu Prabhu

[DocAElstein]

Hello raghuprabhu
Welcome to excelfox

I do not fully understand your question.

For example you say … When I select an item in the form from the dropdown list …. ??
?? - I do not see any drop down list in your file.

For now I will just give you some ideas that may help you to go further yourself.

If you then still need some help, then I think you will need to explain a bit better exactly what you want.

If you or nobody else can help solve your problem, then I can take another look for you tomorrow:
I only have a little time now to give you some ideas… if no one else helps, and you still need help tomorrow, then please give more information about what you want, so that I can help further tomorrow



This is the test range that you have given: ( I have also enclosed your CurrentRegion
as given, for example by Range("A1").CurrentRegion )

_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1
Group
Item Col A
Item Col B


2
1ItemG11ItemG11


3
1ItemG12ItemG12


4
1ItemG13ItemG13


5
1ItemG14ItemG14


6
1ItemG15


7
1ItemG16


8
1ItemG17


9
1ItemG18


10
1ItemG19


11
2ItemG21ItemG21


12
2ItemG22ItemG22


13
2ItemG23ItemG23


14
2ItemG24ItemG24


15
2ItemG25


16
2ItemG26


17
2ItemG27


18
2ItemG28


19
2ItemG29


20
3ItemG31ItemG31


21
3ItemG32ItemG32


22
3ItemG33ItemG33


23
3ItemG34ItemG34


24
3ItemG35


25
3ItemG36


26
3ItemG37


27
3ItemG38


28


29
Worksheet: Sheet2

You wish to somehow get this sort of output , based on the above test data:
_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1
Name
Item
Comments


2RaghuItemG11


3VimItemG16Instead of ItemG16 use ItemG11


4AbhiItemG21


5BhanuItemG25Instead of ItemG25 use ItemG21


6JohnItemG34


7VickyItemG35Instead of ItemG35 use ItemG31
Worksheet: Sheet1

This is your explanation so far…._
_............... In sheet2 of the attached file, I have in column1 have group, column2 has items, column3 also has the same items, some of them missing.

When I select an item in the form from the dropdown list, I want the comment to be populated as shown. If the item selected from column2 is from group1 and is not in column3 then I want the comments column to be as shown.

In the next post I will give you some ideas….

[DocAElstein]

These are just some ideas to get you started.

I will give you a routine which will give you an array, arrOut() , like this

Group Deafault item
1 ItemG11
2 ItemG21
3 ItemG31

The routine is here:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11094&viewfull=1#post11094

Routine brief description

Rem 1
I get the current data region to which includes the first cell in worksheet “Sheet2”

Rem 2
I make an array, arrDtaIn() , of the data


Group
Item Col A
Item Col B

1ItemG11ItemG11

1ItemG12ItemG12

1ItemG13ItemG13

1ItemG14ItemG14

1ItemG15

1ItemG16

1ItemG17

1ItemG18

1ItemG19

2ItemG21ItemG21

2ItemG22ItemG22

2ItemG23ItemG23

2ItemG24ItemG24

2ItemG25

2ItemG26

2ItemG27

2ItemG28

2ItemG29

3ItemG31ItemG31

3ItemG32ItemG32

3ItemG33ItemG33

3ItemG34ItemG34

3ItemG35

3ItemG36

3ItemG37

3ItemG38


Rem 3
' 3a)
I make a string of the unique group numbers “ 1 2 3 “

' 3b)
I make an array of the unique group numbers { 1, 2, 3 }

' 3c) - ' 3d)
I make and fill an array for output, arrOut()

Rem 4
I paste out that array for output, arrOut()

Results
For this input data, before
_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1
Group
Item Col A
Item Col B


2
1ItemG11ItemG11


3
1ItemG12ItemG12


4
1ItemG13ItemG13


5
1ItemG14ItemG14


6
1ItemG15


7
1ItemG16


8
1ItemG17


9
1ItemG18


10
1ItemG19


11
2ItemG21ItemG21


12
2ItemG22ItemG22


13
2ItemG23ItemG23


14
2ItemG24ItemG24


15
2ItemG25


16
2ItemG26


17
2ItemG27


18
2ItemG28


19
2ItemG29


20
3ItemG31ItemG31


21
3ItemG32ItemG32


22
3ItemG33ItemG33


23
3ItemG34ItemG34


24
3ItemG35


25
3ItemG36


26
3ItemG37


27
3ItemG38


28
Worksheet: Sheet2

Now run routine, Sub DefaultItem() ( http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11094&viewfull=1#post11094 )

After running Sub DefaultItem()
_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1
Group
Item Col A
Item Col BGroupDeafault item


2
1ItemG11ItemG111ItemG11


3
1ItemG12ItemG122ItemG21


4
1ItemG13ItemG133ItemG31


5
1ItemG14ItemG14


6
1ItemG15


7
1ItemG16


8
1ItemG17


9
1ItemG18


10
1ItemG19


11
2ItemG21ItemG21


12
2ItemG22ItemG22


13
2ItemG23ItemG23


14
2ItemG24ItemG24


15
2ItemG25


16
2ItemG26


17
2ItemG27


18
2ItemG28


19
2ItemG29


20
3ItemG31ItemG31


21
3ItemG32ItemG32


22
3ItemG33ItemG33


23
3ItemG34ItemG34


24
3ItemG35


25
3ItemG36


26
3ItemG37


27
3ItemG38
Worksheet: Sheet2




So at the end of the routine you have an arrary, arrOut() ,which looks like this

arrOut() =

GroupDeafault item

1ItemG11

2ItemG21

3ItemG31

Select ArrOut then Hit F9.JPG : https://imgur.com/zC6Lin4
2216



If you need more help from me tomorrow, then you must give me more infomation... This : ...... When I select an item in the form from the dropdown list, ... does not suit the file you gave ... or are you refering to the Form instance of UserForm1 ?? Please be more specific. Please tell me exactly what should happen and when it should happen

Alan







Ref
http://www.excelfox.com/forum/showthread.php/1111-VBA-Trick-of-the-Week-Slicing-an-Array-Without-Loop-%E2%80%93-Application-Index

[raghuprabhu]

Hi DocAElstein

My apologies. I did not put the code to open the form on opening the workbook.

When do I call the Sub DefaultItem?

Kind regards

Raghu

[DocAElstein]

Hello raghuprabhu

Sub DefaultItem was not intended to be a final solution to solve your problem.
I still do not fully understand your requirement. So I cannot give a full solution, until I understand exactly what you want

So yesterday, I wrote Sub DefaultItem to help give you some basic ideas. If you try to understand how Sub DefaultItem works, then you may be able to get further yourself.

I do not have much experience with UserForm Forms. But I may revise some of my knowledge on them later today or tomorrow.

If you want me to try to help you further, it would be helpful to me if you do the following in the meantime

Please do this:
_1) Include the code to open the UserForm on opening the workbook.
_2) Please try again to explain clearly want you want.

[raghuprabhu]

Hi Alan,

There is a form included in the attached workbook.

The source list for the combo box is column 2 in sheet2

when I select an item in the combo box for input in sheet1, if the item is not in column 3 of sheet2, say if I select "ItemG16", then I want it to input "ItmeG16" in column 2 and in column 3 input the comment "Instead of ItemG16 use ItemG11"

Alan Thanks for your input

Kind regards

Raghu

PS How do I attach the file the second time?

[DocAElstein]

... How do I attach the file the second time?
Hi,

Uploading file a second time.......
_1) You may need to “Go Advanced”
The normal Start point is with the Paper Clip icon at the top of the Editor Window. That icon will be present in the initial Post Editor, but it may not always be available for further Replys. You may need first to “Go Advanced”
GoAdvancedReplyWindow.JPG : GoAdvanced1.JPG https://imgur.com/1A9qWQM : https://imgur.com/UXBZ4oJ
22172218

_2) _3) Hit Paper Clip and Add Files
PaperClip2AddFiles3.JPG
https://imgur.com/vbPQvTr

_4) Select Files
SelectFiles.JPG
https://imgur.com/aqtVTPa

_5) Upload Files
UploadFiles5.JPG
https://imgur.com/pUfmZc7

_6) Hit Done
Done6.JPG
https://imgur.com/kQAwzao

_-.----
You can also get thereabouts with the Manage Attachments option which you will see when youscroll down after hitting “Go Advanced”
ManageAttachments.JPG
https://imgur.com/KxTxRoC

_.------



( To delete Files:
DeleteAttachment.JPG
https://imgur.com/STzpq9E

_..
You can practice posting here:
http://www.excelfox.com/forum/forumdisplay.php/17-Test-Area
Start a new Thread, with a title such as “Just testing and practicing posting, no reply needed”
You can do anything you want, post anything, Edit, and try again etc… etc.. )


Alan

[raghuprabhu]

Hi Alan,

I have uploaded the workbook with the form opening code.

Thank you for your help.

Kind regards
Raghu

[DocAElstein]

Hello raghuprabhu

This may not be the most efficient way to do the coding, but it if you can learn from it then you may be able to develop a more efficient way to meet your requirements…

_1 ) I have put a Call to the routine , Sub DefaultItem , in the Sub Workbook_Open() in ThisWorkbook code module
( You could possibly replace this routine , Sub DefaultItem , with a formula, to achieve the same results. I do not have much experience with such formulas, but I think it should be possible )
ThisWorkbook Code Module.JPG : https://imgur.com/XPUlCTa
2222

Option Explicit
Public openFlag As Boolean ' This is to stop routine working on open
Private Sub Workbook_Open()

Call Sheet2.DefaultItem
UserForm1.Show
End Sub
This gives us our default items range
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Row\Col
D
E

1
Group
Deafault item


2
1
ItemG11


3
2
ItemG21


4
3
ItemG31
Worksheet: Sheet2


_2) “…. say if I select "ItemG16", then I want it to input "ItmeG16" in column 2 and in column 3 input the comment "Instead of ItemG16 use ItemG11" …..”
Private Sub cboItem_Change() in Form, UserForm1
' a) when I select an item in the combo box for input in sheet1
This is the selected value from Form Userform1….
Private Sub cboItem_Change()
cboItm = cboItem.Value
' b) if the item is not in column 3 of sheet2
We can look for the position along column C in worksheet “Sheet2” of the item. If we do not find it. Then we…………
' c) input "ItemG16" in column 2
Put selected value in the next free row in column B, in Worksheet, “Sheet1”
' d) in column 3 input the comment "Instead of ItemG16 use ItemG11"
' d)(i) determine position along of our selected item down column B in Sheet2
' d) (ii) determine the group number for this item
' d)(iii) use group number in a VLookUp of our default items range to determine the default item number to use
' d)(iv) ".... in column 3 input the comment "Instead of ItemG16 use ItemG11" ......"
Paste in column C , worksheet “Sheet2” , we paste in like:
" Instead of " selected value " use " default item number




Private Sub cboItem_Change() ' ........say if I select "ItemG16", then I want it to input "ItmeG16" in column 2 and in column 3 input the comment "Instead of ItemG16 use ItemG11"
' Stop
If ThisWorkbook.openFlag = False Then: Let ThisWorkbook.openFlag = True: Exit Sub ' This is to stop routine working on open
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets("Sheet1"): Set Ws2 = ThisWorkbook.Worksheets("Sheet2")
' a) when I select an item in the combo box for input in sheet1
Dim cboItm As String: Let cboItm = cboItem.Value
' b) if the item is not in column 3 of sheet2
Dim ItemColB() As Variant: Let ItemColB() = Ws2.Range("C2:C" & Ws2.Range("C" & Rows.Count & "").End(xlUp).Row & "").Value
Dim MtchRes As Variant
Let MtchRes = Application.Match(cboItm, ItemColB(), 0) ' This will return a VBA error value if it cannot find the püosition along ( of cboItm , in array ItemColC() , looking for exact match )
If IsError(MtchRes) Then ' case if the item is not in column 3 of sheet2
' c) input "ItemG16" in column 2
Dim LastItem As Long: Let LastItem = Ws1.Range("B" & Rows.Count & "").End(xlUp).Row
Let Ws1.Range("B" & LastItem + 1 & "").Value = cboItm
' d) in column 3 input the comment "Instead of ItemG16 use ItemG11"
' d)(i) determine position along of our selected item down column B in Sheet2
Dim ItemColA() As Variant: Let ItemColA() = Ws2.Range("B2:B" & Ws2.Range("B" & Rows.Count & "").End(xlUp).Row & "").Value
Dim posItmColA As Long: Let posItmColA = Application.Match(cboItm, ItemColA(), 0)
' d)(ii) determine the group number for this item
Dim GrpNo As Long: Let GrpNo = Ws2.Range("A" & posItmColA).Value
Dim DefItms() As Variant: Let DefItms() = Ws2.Range("D1:E" & Ws2.Range("D" & Rows.Count & "").End(xlUp).Row).Value
' d)(iii) use group number in a VLookUp of our default items range to determine the deafault item number to use
Dim DefItm As String: Let DefItm = Application.WorksheetFunction.VLookup(CStr(GrpNo), DefItms(), 2, 0)
'Dim Item() As Variant: Let Item() = Ws1.Range("B2:B" & LastItem & "").Value
' d)(iv) ".... in column 3 input the comment "Instead of ItemG16 use ItemG11" ......"
Let Ws1.Range("C" & LastItem + 1 & "").Value = " Instead of " & cboItm & " use " & DefItm & ""
Else ' case the item is not in column 3 of sheet2

End If





End Sub



Example in next post

[DocAElstein]

Example for …._
_.. this data in ..
Sheet 2
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1
Group
Item Col A
Item Col B
Group
Deafault item


2
1ItemG11ItemG11
1
ItemG11


3
1ItemG12ItemG12
2
ItemG21


4
1ItemG13ItemG13
3
ItemG31


5
1ItemG14ItemG14




6
1ItemG15




7
1ItemG16


8
1ItemG17


9
1ItemG18


10
1ItemG19


11
2ItemG21ItemG21


12
2ItemG22ItemG22


13
2ItemG23ItemG23


14
2ItemG24ItemG24


15
2ItemG25


16
2ItemG26


17
2ItemG27


18
2ItemG28


19
2ItemG29


20
3ItemG31ItemG31


21
3ItemG32ItemG32


22
3ItemG33ItemG33


23
3ItemG34ItemG34


24
3ItemG35


25
3ItemG36


26
3ItemG37


27
3ItemG38
Worksheet: Sheet2

Sheet 1 Before
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D

1
Name
Item
Comments


2AlanItemG16Instead of ItemG16 use ItemG11


3AbhiItemG21


4BhanuItemG25Instead of ItemG25 use ItemG21


5JohnItemG34


6VickyItemG35Instead of ItemG35 use ItemG31


7VimItemG18


8
Worksheet: Sheet1

Now select from drop down box , Item 24
Item24.JPG : https://imgur.com/BDsmk3A
2221

After : ( no change )

_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1
Name
Item
Comments


2AlanItemG16Instead of ItemG16 use ItemG11


3AbhiItemG21


4BhanuItemG25Instead of ItemG25 use ItemG21


5JohnItemG34


6VickyItemG35Instead of ItemG35 use ItemG31


7VimItemG18
Worksheet: Sheet1

Now select from drop down box , Item 25
Item25.JPG : https://imgur.com/fyPtICC
2220

After:
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D

1
Name
Item
Comments


2AlanItemG16Instead of ItemG16 use ItemG11


3AbhiItemG21


4BhanuItemG25Instead of ItemG25 use ItemG21


5JohnItemG34


6VickyItemG35Instead of ItemG35 use ItemG31


7VimItemG18


8ItemG25 Instead of ItemG25 use ItemG21


9
Worksheet: Sheet1



Alan

[raghuprabhu]

Hi Alan,

This is what I am looking for. Thanks a ton!

I will play around with it and see it works and get back to you

Kind regards

Raghu

[DocAElstein]

Thanks for the feedback, Raghu, and to let me know how and/or if it works :)
Alan