Need help creating VBA or a tool for Excel project

[VegaOne16]

Hello,

I'm working on a project and wanted to see if someone could help me, either by using VBA or another way.

I'll have data in a worksheet name "Data" in Column C.

Lets say column C contains the following:
Row 1 = Bank of America
Row 2 = Company Deposit - John Doe Company
Row 3 = Denny's

I want excel to be able to do the following for me in Column A of the "Data" worksheet:
(#1) If cell in column C is blank, skip this row and go to the next row.
(#2) If cell in Column C contains (have it search through all search criteria on another worksheet named "Search Criteria), then put in this cell what is listed in column B of the "Search Criteria" worksheet.
(#3) If excel can't find any matching criteria on the "Search Criteria" worksheet, then enter the word "Manual" into the cell.

Note: I don't want it looking for an exact match. So if cell C said, "Bank of America", and I had "America" listed on the "Search Criteria" tab, I'd want it to pull over the data.

So lets say I'll have another worksheet called "Search Criteria" with the following criteria and results:

|Col A|--------------|Col B|
|America|------------|1000|
|Wal-Mart|-----------|2000|
|Company Deposit|---| 3000|

1) So I'll push a button, excel will go down each row in column A of the "data" worksheet...
2) if the cell in column C of that row is blank it'll just go to the next row...
3) if there is data in column C of that row, it will search column A on the "Search Criteria" worksheet and if it finds the criteria, it will take the result of column B of that worksheet and pop that into the cell of the "Data" worksheet...
4) if it doesn't find any matching data in the search criteria, I want it to put the word "Manual" into the cell on the "data" worksheet.

The end result will be on the "Data" worksheet:
|Col A|---|Col B|--|Col C|
|1000|----||-------- |Bank of America|
|3000|----||-------- |Company Deposit - John Doe Company|
|Manual|--||--------|Denny's|

Is something like this possible?

I'm using excel 2007.

Thank you for your help!

 

[MikeWx]

Hi VegaOne16,

Welcome to the message board.

I have the search criteria in a separate sheet of the same workbook, and called the sheet Search Criteria.

Search Criteria


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 117px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>America</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Wal-Mart</TD><TD style="TEXT-ALIGN: right">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Company Deposit</TD><TD style="TEXT-ALIGN: right">3000</TD></TR></TBODY></TABLE>


This is what my Data sheet looks like:

Data


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 251px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1000</TD><TD></TD><TD>Bank of America</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3000</TD><TD></TD><TD>Company Deposit - John Doe Company</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>MANUAL</TD><TD></TD><TD>Denny's</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">2000</TD><TD></TD><TD>Africa Wal-Mart</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

My array formula (press Control Shift Enter after entering the forumula) in Cell A2 is:

=IF(C2="","",IFERROR(INDEX('Search Criteria'!$B$2:$B$4,SMALL(IF(ISERROR(SEARCH('Search Criteria'!$A$2:$A$4,C2)),"",ROW($A$1:$A$3)),1)),"MANUAL"))

Is this what you are trying to do?

Mike

 

[VegaOne16]

Hi Mike,

Thanks for your response.

Could you walk me through the keystrokes you made on how you got the numbers to auto populate in Col A of the data tab please.

Thanks!