Hi,
I'm a beginner Excel user and could really use your help on my project.
I need to generate a form letter from values on another sheet. I'm using vlookup and as you know, for duplicate values, only the first reference is given. I have several duplicates and need to pull the correct row data for each instance. I would need to replace each vlookup with the proper formula in my letter.
Can you please look at the sample file and give me some guidance?
Thanks in advance.
Jean
Hi Jean, I think this is a job better suited to a Microsoft Word "mail merge".
You will end up with far more professional looking letters and it's a simple thing to do.
I agree that Word's MailMerge is a better choice. However, if you wish to eliminate duplicates in a column of values, you can use the Advanced Filtering capabilities of Excel
- highlight/select your data on "Inputs" where you have the duplicates (in your case, highlight A2 through A10)
- click on the Data tab
- In the "Sort & Filter" area, click on "Advanced"
- in the box that shows up, click the button for "Copy to another location"
- in the "Copy to:" box, insert a cell where the filtered data should be placed (e.g., I2)
- click the "Unique records only" checkbox
- click "OK" (you may get a message about including more data in your filter; just say No)
You should now have a list of unique values starting in Cell I2. Use this list as the input to your dropdown in Cell G7 on your "Letters" tab.
Hope that helps.
Where there are duplicate Application #s which of the #s do you want to pick up?
i.e. What do you regard as the 'CORRECT' row data?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Would it be possible to use the "Receipt Number" as the lookup key rather than the "Interview Number"?
In the small sample you provided they would appear to be unique.
Thank you but the users find Excel to be a better way to input the data, and that's what the boss decided...
Last edited by stama100; 04-09-2014 at 08:30 AM.
Thank you for your suggestion, however, I cannot eliminate the duplicates, they need to be kept as part of our records. The objective is for me to provide this file and allow several users (who know even less Excel than me), to make the inputs in the data table, and then go to another worksheet and find the respective Application # (on a different date, please see "Karla" example in my file) to fill the form letter and they can print it and send out. The problem right now is that vlookup only looks up the first instance of the record (not the 2nd or 3rd). Is there an INDEX or MATCH or ROW function combination that I can use?
Last edited by stama100; 04-09-2014 at 08:31 AM.
The correct row data would be the respective date info based on the application #. (vlookup only allows to find the first instance, I need a formula that can pull the respective row info to the interview # -- there are several "Karla" entries in my sample file).
Sorry, in my project we have to use the interview number as the identifier for the record... (that's the challenge...)
What about re-ordering the list based on column E so the most recent Date of Request becomes the first one VLOOKUP will find?
....and what's the answer to post #4? i.e. given several occurrencies of an application # which one do you want to use?Originally Posted by stama100
Where there are duplicate application #s, the user needs to be able to select the 2nd or 3rd instance of that application # (from the drop down list) that corresponds with that particular row data (the "correct" row data) to populate the form letter.
I've been able to "identify" the location of the duplicate values but I'm stuck and don't know what to do next. Once the location of the corresponding row is identified (that aligns with row data from Interview # drop down list), it needs to pull the corresponding (regardless of duplicate) Receipt #, interview date, etc. Help please... Thanks in advance.
={SMALL(IF(Inputs!A2:A10=$G$7,ROW(Inputs!A2:A10)),ROW(Inputs!1:1))} = 6
={SMALL(IF(Inputs!A2:A10=$G$7,ROW(Inputs!A2:A10)),ROW(Inputs!2:2))} = 8
={SMALL(IF(Inputs!A2:A10=$G$7,ROW(Inputs!A2:A10)),ROW(Inputs!3:3))} = 10
Last edited by stama100; 04-09-2014 at 12:39 PM.
Hi,
I think you are misunderstanding the use of a drop down list. It's usually used to hold a list of unique values. There's no parameter or function that identifies which particular 'row' in the drop down you have picked.
But let me ask you a more fundamental Q. How does the user know which of the non unique Application numbers to pick?
....
I'd be inclined to let the user double click a row in the Inputs sheet and have a Sheet_Change event macro populate the relevant fields in the Letter - either directly from the code or populating just the Unique Receipt Number and using an INDEX(MATCH()) combination to pick up the other details.
I'm using the drop down list as the only item that user is able to select. All other formulas (in the form letter) would pull the respective row data from what the user selects. The form letter is locked to avoid any inadvertent changes by different users. The user knows which non-unique appplication #s to pull as desired based on more recent entries or as needed. Is there any way to do this??
a repeat of above.
I know what you are trying to do, and you can't do what you are trying to do.
As I explained the position of the item number that you happen to choose in the drop down is not recorded in any way.
I'm intrigued though to know how the user knows which non duplicate item to pick. You say the user knows how to pick the appropriate item # but haven't explained how. Please explain the thought process that a user goes through when presented with the form letter. So they know the application number, OK I understand that, but then what....?
How do they know unless they have access to some other information which identifies that they need to pick the 2nd, 3rd or whatever instance of that number.
The user makes the entries into the Input area. The same user needs to pull the application # from the pull down list to populate the form letter because s/he needs to print the form letter for the applicants' information just entered. When an applicant makes an appointment, s/he is assigned a unique application #. The applicant can make several appointments, which result in different dates and receipt #s. The user then determines to send a form letter (I have not included the various form letter types, only one), based on decisions about the applicant. But the current VLOOKUPs only find the first instance of the data. I need to pull subsequent data to populate the form based on user's choice. Hope that clarifies anything I left out. Any insight on how to best achieve this?
If you open my sample file in the Letter tab, you can select the various entries for Karla (3 instances, in the pull down menu on G7) but the letter populates only the first instance of the data. The user needs to pull the 2nd and 3rd, etc. instances of Karla that appear with the respective dates on the Inputs tab (from the pull down menu on Letter tab, G7) so the form letter populates respectively. Any suggestions?
Which presumably means the user has to mentally make a note of the instance of the Application number in question before switching to the Letter tab.
Why bother. Since the user is already on the Inputs tab why not just double click the appropriate line and have the letter automatically updated.
See attachment. To repeat just double click any row in the Input tab.
Hi,
I don't know how I can explain this in any other way. There is nothing that tells you the position in the validation drop down box of the selected item. Since the system doesn't know this then your examples in post #13 are irrelevant since the system doesn't know the Inputs! Row.
There is only one way you could use this approach and that's to use an Activex ComboBox or ListBox object instead of the simple Validation drop down. But as I said in my other post what's the point if the user is starting on the Inputs sheet in order to decide which Application number to pick. Just use the Before Double click event of the Input Sheet.
What's the "magic" behind the double clicking on the rows? I'd like to copy the code to other tabs (other letter templates) in the file. And how do you "tag" the fields in the letter to change when the Inputs row is double clicked?
The "magic" is called a macro.
Hi,
As minnesotaart has mentioned this approach uses a macro - a piece of Visual Basic code. Not sure how much you know about the hidden delights of Excel so apologies if I'm starting to teach Grandma to suck the proverbial eggs.
Excel has 'behind' it a rich piece of software called Visual Basic (for) Applications. Known as VBA or more commonly macros. This allows you to automate workbook processes and build whole systems of decision driven operations. At its most rudimentary you can capture VBA code by using the Macro Recorder which you'll find in the Ribbon under the Developer Tab. And if you don't see the Developer tab go to Excel options and select the Customise Ribbon to make it 'live'.
In your particular case we're using what's know as the sheet's 'double click' event code, i.e. a piece of code which runs once any cell on the sheet is double clicked. There are lots more events I just happened to choose this one as being perhaps the most obvious. You could have a right click event, a change event or others.
Here's the code.
You'll see that all the cell references are hard coded. This is generally bad practice, I did it for simplicity. It's much more efficient to give range names to specific cells like G7, G8 etc. and use the names in the code. Which is sort of obvious when you think about it since if you were to add a new row to the letter sheet above G7 this code would immediately be compromised and it wouldn't work, whereas if it use range names it would.
Anyway, what this macro essentially says if the cell being clicked (known here as the target cell) is anywhere between A2:F100 then carry on with the macro. Furthermore if its equal to Nothing then end the macro.
Assuming these two tests are satisfied then it goes on and makes cell G7 on Sheet2 (the Letter page) equal to cell Ax where x is the row number of the cell you happen to have double clicked.
Please Login or Register to view this content.
I'm a novice in VBA.
How do we adjust the code to include the other worksheets, as attached?
How do we adjust the code to include the other worksheets, as attached in prior post?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks