Free Microsoft Excel 2013 Quick Reference

Concatenate and vlookup Results

1.I have a data table of US counties 3200 rows and 56 columns.
2.Have a store listing of 500 units
3.I am printing call record forms for use by reps that make calls on the
units.
4.The actual worksheets fill in info on this call record form, triggered by
a store number (lookups data from#2 above, e.g. address, phone#, etc), this
becomes a single page, with space for comments etc, without having to write
in all the contact data. Simply enter the store # and the form is printed
out and ready for use.
5. This call record form is 12 columns wide to contain all the info and to
fit on 8.5x11 sheet of paper.

The question: The new county data I want to include on this sheet is 15-20
columns of data; which is too wide to fit on the call record. Is it possible
to use the camera function to "snap" say columns 25-45 and paste it to the
call sheet to fit in a specified area? (This data would be static and used
only for reference, no math performed.)
Can VBA do this? Obviously, I want to automate this, so that when a new
call record form is printed, this new county data would be on the sheet.

This would be similar to using Vlookup and Concatenate to pull the coulmns
of data...I'm looking for an easier way. Could these Coulmns be Named, and
the use Vlookup to pull the named range as "one piece" so as to fit in the
space allowed?

Appreciate any thoughts.

Hello,

I need to find all the text strings, which matches to certain criteria.
Briefly, I have a criterium "test". Then, I'm interested in the rows which
have "test" string in a column B. I'm looking for the text strings in the
column C, which correspond the rows according to "test" criterium. I have to
eliminate all the duplicate and blank values and finally concatenate rest of
the values.

VLOOKUP just returns the first match, which can even be blank. In theory, I
could use the OFFSET and MATCH -functions for all the matches, changing the
reference to correspond the next row from the previous match and continue.
However, there are so many matches that it would be impossible to link so
many loops.

DGET would be an ideal function, only if it could handle the multiple
matches. If there was an IS-function to #NUM!- and #VALUE errors
particularly, I could determine, if there are matches for the criteria.

Array functions don't seem to solve this problem either, because the rows
which corresponds the criterium are in disorder, so I can't get an array,
which would correspond the criterium.

I am attempting to take a label with numbers and dashes and remove the
dashes, so I can convert the labels to the same format as my source file in a
VLOOKUP. I'm using the following formula:
=CONCATENATE("'",left(a1,1),mid(a1,3,5),mid(a1,10, 3),right(a1,1), which takes
0-16830-400-7, and converts it to '0168304007. The problem is, the VLOOKUP
doesn't work until I value the formula, then hit F2 and F9 to convert it to a
label. The problem is the ' symbol, which shows in the cell until I hit F2 &
F9. If I don't put the ' symbol into the CONCATENATE formula, CONCATENATE
converts the 0168304007 to a value and removes the leading zero. My source
file is a label that contains a leading zero.

Is there a better way to do this conversion? If not, is there a way to
change the result to a label without manually hitting F2 & F9. I'm
converting a few thousand labels, so doing this manually would be a nightmare.

Hi all,

I have a spreadsheet that starting at row 18 contains the following
information
A18=M$2
B18= Datavalidation indirect ($M$2)
C18=IF(B18="No More
Options",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_ info")),1,FALSE),IF(B19

I would like to return all values from a data set to a single cell. The
list would look concatenated with a semi-colon - as in
value1;value2;value3. Not sure how to do this...

Everything I'm reading is returning single values (as a vlookup does).
But my data set has more than 1 value that meets the criteria and I'd
like to return them all (preferrably in a single cell).

Any help would be welcomed.

Thank you.

--
Jim Burns
------------------------------------------------------------------------
Jim Burns's Profile: http://www.excelforum.com/member.php...o&userid=24389
View this thread: http://www.excelforum.com/showthread...hreadid=379834

I"m working on an inventory sheet which constantly changes in size.
If there is a way to do this with Functions, i would rather that because
this is a report that is run over night when i'm not here. If not, i guess
we can find a way.

I'd like to look down column A and look at every single item.
If there is no duplicate, do nothing
If there are duplicates, it should first find where A and B = each other for
that particular #. Then take all the cooresponding #'s in B and concatenate
them in Column C (each seperated by a colon) on the row where A and B were =.
see below for a visual.

examples
A1 = 364691-001 B1 = 364691-001 C1 = 364692-001:364695-001
A2 = 364691-001 B2 = 364692-001 C2 = (empty cell)
A3 = 364691-001 B3 = 364695-001 C3 = (empty cell)
A4 = A3509A B4 = A3509A C4 = (empty cell)

A1,A2,A3 are duplicates
A1 and B1 are equal to eachother
Take B2 and B3 and concatenate in C1.
C2 and C3 can stay empty
A4 and B4 = eachother, but there are no duplicates so C4 stays empty

I"m not sure if indexing, Vlookup, if, then, next and loop will be needed or
not.
I'm open to any suggestions.

Greetings,
I have over 400 rows of data where Column 1 is text (project titles) and
Column 2 is a start date and Column 3 is an end date. In each of the date
columns, there are duplications.

I want to get these start and end dates into another spreadsheet which is
formatted like a calendar. However, I gather VLOOKUP on its own won't work
with the duplications, and when the dates in the first spreadsheet aren't in
chronological order.

There may be other ways of solving this, but my immediate thoughts were to
insert a new sheet search for all duplications in Column 2 and then
concatenate the corresponding text in column 1. I then need to find some way
of getting the concatenated text into the right day in the calendar.

Any suggestsions gratefully received.

Currently I use alot of things like
=CONCATENATE("A",MATCH(B2,A:A,0)+1)

This will return something like A6 when im searching for yesterdays date and
I want to know the cell reference where today's date should go. Although,
thats a simple example of the macro's I use that puts information in certain
cells depending on what stuff is in other place.

My Question is, that this is fine for me, but when it comes to a Horizontal
match instead of a vertical one the answer is still a number. This leaves me
having to dedicate over 200 lines to do a vlookup on this number, so 3 = C
and 28 = AB.

Is there anyway to automatically determine from a number which column it is?
So I can use it in concatenated matches. What im currently working on looks
as 1:1 for "Total" then minus's 1, so each time the total is moved right for
a new column my information will go in this column.

Cheers for your time!

I have a complicated master inventory spreadsheet from which I need to
generate shorter reports for items currently in use. My column headings are
as below:

A Product
B Color
C Volume Used
D Manufacturer
E Catalog Number
F Lot number
G Date received
H Expiration Date

However, we may have a given product in several different colors.

All 8 columns need to show up in my report.

I would like to be able to use VLOOKUP (or something else that works) to
find (for example) Product X in green and then be able to drag the formula
to return the rest of the 7 columns as well. If I concatenate first, I can't
drag. If I just link the cells then the report gets jumbled up every time
somebody sorts the inventory sheet. If I have to type in a separate vlookup
formula in each column, I'll still be typing this time next year. I know
this really should be done in Access, but unfortunately, that's not an option.

Any help would be greatly appreciated.

Thanks,
JenL

1. Why does a formula SOMETIMES act as a formula and in other work
book/sheet act as text? ie will produce "Joe Bloggs" if A3="Joe" and
A2="Bloggs" but in another worksheet will remain "=CONCATENATE(A3," ",A2)"
2. How do I lookup a column of text which are a result of formula? eg.
=VLOOKUP(A2,NAMES,2) where A2="Joe Bloggs" and NAMES=Array of results of
formula "=CONCATENATE(A3," ",A2)". Currently produces "N/A" even though I
KNOW that Names contains "Joe Bloggs".
Any help with this would be greatly appreciated as I only have one or two
handfulls of hair left!!
--
Bob JONES

My getpivotdata formula is not working in all instances within my spreadsheet. The formula has been copied down, and it works in the majority of cases, but not all. It is basically returning zero value when there are figures contained within the pivot table.

The name that changes depending on the row in the getpivot formula is obtained from a vlookup, which the data in the pivot table is also based on. So if they're the same, it should be returning the figures, not zero...

I thought it must be a formatting issue, but I've copied and pasted so that I'm sure the fields are the same...

Any suggestions on what I could do??? Please let me know if you need more info

Many thanks!!!

PS Using excel microsoft office 97 pro

Here's an example of my formula, if this helps

=IF(ISERROR(GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$A$6," "))),0,GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$A$6," ")))+IF(ISERROR(GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$B$6," "))),0,GETPIVOTDATA('Prem PT'!$C$5,CONCATENATE(Test!C6," ",D6," ",Test!$B$6," ")))

First time doing this.. but.. This is worth 30usd to me? (I'm guessing paypal is easiest?)

I haven't found online examples quite like what I'm trying to do, but I think it's do-able and quite easy?

I have a worksheet where each row is an individual. To the right of this I want some users to populate the employee numbers of who they feel could replace them if that individual left the company.

Because we want these replacements as employee numbers (so that we can populate additional sheets/do vlookups), and the number of possible candidates is quite large (1-2 thousand), I want to provide a search form they would use to find each replacement. This will allow more accurate data entry while still keeping the document in excel format, which they are familiar with.

So.. What the data looks like (as per first attachment example):

Sheet 1:
User,~extra data~,Replacement 1,Replacement 2, Replacement 3 etc....

Sheet 2:
Employee ID, First and last name, Last Name, First name, ~extra data~

Note that the last/first names on sheet two will be calculated (I'll use a formula to extra the First/Last names from the concatenated name in the 2nd cell). I'm doing this as I believe to allow searching on first/last name separately they should be in different columns.

Functionality desired:
Triggered by either clicking the "replacement" cells, or selecting the cell and starting the macro (ctrl function perhaps?) I'd like a search form to come up.

Search form would show 2 boxes:
-First Name
-Last Name
and a search button.

Entering either or both lists any matches (using wildcard search) and their matching employee number. If the user doubleclicks any of the results, that user's employee number is pasted into the originally highlighted "Replacement" cell.

Workflow Summary:
-User clicks one of the "replacement" cells on the first worksheet and starts the macro. If clicking the cell it'self would trigger the macro/form (or perhaps hitting a combination of keys) that would be ideal.

-Search form appears.. User enters first and or last name and clicks search.. matching results (based on first/last name from 2nd worksheet appear) **Note, would be desirable to show results as their first/lastname plus their employee number.

-User clicks the correct user (if there are multiple results).

-That employee's employee number gets populated to the cell they selected before starting the macro.

I've attached a simple workbook showing what the data looks like:

Closest I found to this was here:
http://www.ozgrid.com/forum/showthre...t=27799&page=2

But that one returns the sheet the matching user(s) is on, then brings you to that sheet/row if you click it.

What Excel functions changed your life/career?

I have been an observer of this forum but recently joined (because I needed some help!).
And, I was just able to offer my first piece of advice back to the community. Woo Hoo!
The evidence

I have a long way to go in my Excel journey I was just wondering what functions changed people's life in terms of career and outlook on how to use Excel.

IF
Where would I be without If?
Such a great function that allows multiple options in one cell.
Changes the way I processed information

VLOOKUP
I was transferring data manually in one job and my manager at the time and asked how I was doing.
I was saying it was going finr. Although, I was kind of new to the job and was struggling to see how I could do this all in a data especially without mistakes.
He came round and asked how I was doing it. He asked if I knew what the VLOOKUP function was.
I said no. He sat down with me and I was literally shocked, surprised and amazed.
When I show other people who have a similar problem the look of wonder that appears on their face is child-like!
Deffo one of the best functions I think that shows people why Excel can be so powerful.

SUBSTITUTE/TRIM/CONCATENATE/LEFT/RIGHT
I am almost always using the above functions to cleanse my datasets
Often getting them ready to be vlookupped!

INDIRECT
This is such a neat little feature. I must have gone a solid year without ever having come across it.
Sometimes you may have ten different ranges e.g. Admissions to different hospitals over a year separated by months.
You have a formula you want to run and then you want to give the user option to see the different values dependent on what they choose in filter.
But, your formulas are dependent on a range. How do you change the range dyamically?
I'm doing a rubbish job at explaining it but follow this video here http://www.youtube.com/watch?v=L6Ow1wLfw2Q

Anyway, just thought I would ask: What Excel functions changed your life?

I am particularly interested what got you to use VBA as I have tried to learn it but I usually give up and decide let me go with a formula instead.

I need an example of how to concatenate two column into one drop down box and
how to do a VLookup for a drop down box. Thank you.

I am using the same formula on several different worksheets. Some work and
some return #N/A as a result. Any suggestions? Here is my formula:

=IF(ISERROR(VLOOKUP((CONCATENATE($C42,"INSQUOTE-AUTO")),Pronto2!$1:$2500,8,FALSE)),"",VLOOKUP((CONCATENATE($C42,"INSQUOTE-AUTO")),Pronto2!$1:$1940,8,FALSE))

Thank you. What I am working on is a document we use at work to tally
production
and in this case there are 20 items. An example would be if item 301 were
completed, it would return a production value of 1.5. Here is the basic idea

=IF(B3=301,1.25,IF(B3=302,1.5,IF(B3=303,1.5,IF(B3=304,1.75,IF(B3=305,1.25,IF(B3=306,1.5,IF(B3=307,0. 75,IF(B3=308,0.75,))))))))

"Peo Sjoblom" wrote:

> It's possible to use IF function 20 times given that you concatenate each
> part, however it is hard to audit and if you indeed need many conditions you
> might be better off using something else like a lookup table or index,
> here's an example of how to bypass the 7 nested limits
>
> =IF( A1=1,"a","")&IF(A1=3,"c","")&IF(A1=5,"e","")&and so on
>
> however you might as well use a vlookup formula
>
> =VLOOKUP(A1,{1,"a";3,"c";5,"e"},2,0)
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "JimB" <JimB@discussions.microsoft.com> wrote in message
> news:938978BB-AD3C-4517-9880-EF8ADA233ED8@microsoft.com...
> > Do you know of any of creating formulas using the "if" function more than
> > seven times. It is normally limited to seven, I need as many as 20
>
>

Hi! I am currently working on a spreadsheet, that has the following formula inserted:
=IF(ISERROR(VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE)),"",VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE))
That formula works fine.

However, when I add in an additional IF statement (to see whether a specific criteria is met) in order to reduce the processing time, I don't get the correct value, and end up with 0.

=IF(AA8="Y",IF(ISERROR(VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE)),"",VLOOKUP(CONCATENATE($A8,AW$3),'Inbound Baseline 2010'!$B:$V,21,FALSE)),0)

Any ideas of what is causing this to happen?

Any help would be greatly appreciated! Thanks!

I have set up an excel file that helps put together different text components using the vlookup and concatenate formulas for use in one plain text document .

When I try to paste the finished result into notepad/textpad/word it adds extra double quote marks throughout the text. Does anyone know how to paste it without these marks?

Thanks!

Hello,

New forum member here. I'm trying to create a formua that will reference two variables (so both variables must hold true for this to work) and sum the dollar values for all true instances. The formula will reference a set of data that is imported into a file monthly. (This is obviously a budget file that I'm working on.)

I think the formula will require an if-then statement, vlookup and sumif, but so far I haven't had much luck pulling all three of these functions successfully into one formula that will work. I'm also using the concatenate function to blend the two variables together. So this is fairly complicated. My fear is that it's beyond Excel's capability.

Has anyone ever used these formulas together successfully for this purpose? Please let me know if any of this is unclear. I've attached the file and highlighted in yellow the cells that I need looked at.

I appreciate your help and suggestions.

Thank you.

Dan

I have created a very large excel file that takes at least 1.5 minutes to open and .5 minutes to calcuate when refreshing using macros.

I am looking for help to reduce the size of the file which will help me to be able to access it more frequently since it takes over and freezes all other excel files and makes the entire computer run slowly.

My spead sheet is approx 330,000 MB. Not sure if I can even send that large of a file through the internet so I have not attached a link or the file on this page.

I have created an allocation system. In short, I am tying in open component purchase orders, with current inventory and running them against our production schedule to determine the date the components will be available by sales order.

The major constraints are that we have multiple sales orders that can produce using the same component on multiple lines on the same day.

To be short, the main table which uses the most memory contains the following formulas:

INDEX
IF
VLOOKUP
OR
ISNA
CONCATENATE
SUM

I use the same formulas for each part number. There are 260 parts. The primary fomula totals all the previous usages for the same component and compares it to the daily inventory.

Which is 159 columns x 152 rows per part.