Newbie's first question

[elvezrulz]

This is probably an easy one for you experienced Excel users. What I'm looking for is probably a Vlookup function. Basically, I create sales reports and our database only pulls from things that have been sold. I need an excel formula that will look at my report and compare it to a list of total available SKU's and spit back out a list of items not found on that list, in other words things that were not sold. That way I can tell my sales team what things they need to work on more easily. Right now I have to do it manually and it's a real pain in the keester. Can anyone help? Thank you in advance! michael

 

[elvezrulz]

Interesting Vlookup question-please help

I put his on here previously, but my title was too ambiguous.

What I'm looking for is probably a Vlookup function. Basically, I create sales reports and our database only pulls from things that have been sold. I need an excel formula that will look at my report and compare it to a list of total available SKU's and spit back out a list of items not found on that list, in other words things that were not sold. That way I can tell my sales team what things they need to work on more easily. Right now I have to do it manually and it's a real pain in the keester. Can anyone help? Thank you in advance! michael

 

[hiker95]

Re: Interesting Vlookup question-please help

elvezrulz,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[Ron Coderre]

Perhaps this example will help....

Col_A contains this list in A1:A16
Sold_Items
Bravo
Echo
Foxtrot
Golf
Juliet
Mike
November
October
Quebec
Romeo
Sierra
Victor
Whiskey
X-Ray
Yankee

Col_B contains the list of all items in B1:B27
All Items
Alpha
Bravo
Charlie
Delta
Echo
Foxtrot
Golf
Hotel
India
Juliet
Kilo
Lima
Mike
November
October
Papa
Quebec
Romeo
Sierra
Tango
Uniform
Victor
Whiskey
X-Ray
Yankee
Zulu

These regular formulas, when copied down, create the list of unsold items:
If you have Excel 2007 or later

D1: =IFERROR(INDEX($B:$B,SMALL(INDEX(ROW($B$2:$B$27)+
(COUNTIF($A$2:$A$16,$B$2:$B$27)>0)*10^10,0),ROWS($1:1))),"")

or...if you use Pre-Excel 2007

D1: =IF(ROWS($1:1)<=(COUNTA($B$2:$B$27)-COUNTA($A$2:$A$16)),
INDEX($B:$B,SMALL(INDEX(ROW($B$2:$B$27)+(COUNTIF($A$2:$A$16,$B$2:$B$27)
>0)*10^10,0),ROWS($2:2))),"")

In the above example, the formulas return this list:
Alpha
Charlie
Delta
Hotel
India
Kilo
Lima
Papa
Tango
Uniform
Zulu

Is that something you can work with?

 

[Joe4]

I merged your two threads together. In the future, please do NOT start a new thread. Simply reply back to your original thread with the clarifications. Per forum rules, threads of a duplicate nature are typically deleted or locked.

Thanks.

 

[elvezrulz]

I merged your two threads together. In the future, please do NOT start a new thread. Simply reply back to your original thread with the clarifications. Per forum rules, threads of a duplicate nature are typically deleted or locked.

Thanks.

Ooops. Sorry. I read the replies and am trying to upload a sample of what I'm trying to do. I am using an old version, 2002. how do I upload a file?

 

[Joe4]

No problem, I see you are a newbie.

how do I upload a file?

You cannot upload files to our site. However, you can post screen images using one of the tools mentioned here: http://www.mrexcel.com/forum/showthread.php?t=508133

 

[hiker95]

elvezrulz,

I read the replies and am trying to upload a sample of what I'm trying to do. I am using an old version, 2002. how do I upload a file?

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[elvezrulz]

Perhaps this example will help....

Col_A contains this list in A1:A16
Sold_Items
Bravo
Echo
Foxtrot
Golf
Juliet
Mike
November
October
Quebec
Romeo
Sierra
Victor
Whiskey
X-Ray
Yankee

Col_B contains the list of all items in B1:B27
All Items
Alpha
Bravo
Charlie
Delta
Echo
Foxtrot
Golf
Hotel
India
Juliet
Kilo
Lima
Mike
November
October
Papa
Quebec
Romeo
Sierra
Tango
Uniform
Victor
Whiskey
X-Ray
Yankee
Zulu

These regular formulas, when copied down, create the list of unsold items:
If you have Excel 2007 or later

D1: =IFERROR(INDEX($B:$B,SMALL(INDEX(ROW($B$2:$B$27)+
(COUNTIF($A$2:$A$16,$B$2:$B$27)>0)*10^10,0),ROWS($1:1))),"")

or...if you use Pre-Excel 2007

D1: =IF(ROWS($1:1)<=(COUNTA($B$2:$B$27)-COUNTA($A$2:$A$16)),
INDEX($B:$B,SMALL(INDEX(ROW($B$2:$B$27)+(COUNTIF($A$2:$A$16,$B$2:$B$27)
>0)*10^10,0),ROWS($2:2))),"")

In the above example, the formulas return this list:
Alpha
Charlie
Delta
Hotel
India
Kilo
Lima
Papa
Tango
Uniform
Zulu

Is that something you can work with?

Thank you, I think so. So would I copy and past that formula in multiple cells, or will it populate all of the information with one lookup?

 

[Joe4]

You can upload your workbook to www.box.net and provide us with a link to your workbook.

Yes, that is another option, though not our preferred one, as many of us are not allowed to download files off of the internet from our places of employment (any many other people simply will NOT download files, for security reasons or other reasons). Also, there is also the possibility of links becoming "dead" if the files are ever removed.