Using text as data

[bob122]

Okay so I need some help here.

[TABLE="width: 300"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Drink[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Oil[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to either:

1. Convert certain words into numbers. E.g "apple", "orange" and "banana" into a 1. "Chicken" into 2, "Oil" into 3, and "Drink" into 4. So it will look like this:

[TABLE="width: 300"]
<tbody>[TR]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

OR

2. I want to sum each of the categories. For example: (Apple + Orange + Banana = 28) and (Chicken = 3) and (Oil = 4) and (Drink = 4).

Things to know:
The data that I need this to work on contains over 1000 rows - and has around 100-150 categories. Depending on the data that I load, not all categories are always present.

Things I have tried:
I have tried the sumif function. However the problem that I have noticed is that the formula needs a specific range to work with - and I can't guarantee that the range will always be the same. The bigger problem is that the sumif function doesn't seem to allow more than 3 categories at a time.

 

[SpillerBD]

To convert your 'Text' to values, you should go with a Lookup table.
Your Sumif, Range issue Should be solvable using Table methodology (CTRL+T) to create a table from a range.
In newer versions, SUMIFS is available.

 

[bob122]

I tried for hours and hours playing around with all that stuff. So far I have thought about using the Find and Replace function to do exactly what I want. This VBA script here seems to be doing the job:

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault


Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long


fndList = Array("CONFECTIONERY - BARS", "CONFECTIONERY - SUGAR", "CONFECTIONERY - GIFTING")
rplcList = Array("4", "4", "4")


'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht

Next x


End Sub

Now I've just got to add the hundreds of categories by hand and I'm assuming I'll be able to run the script in any file. Just thought I'd post the solution just incase anybody else ever wants to do what I wanted

 

[bob122]

Sorry for bumping. Okay the VBA script as above has a problem - if the fndlist array isn't in the worksheet then the script comes up with an error. Does anybody know how to solve this? For example in the above script of "confectionery - bars" isn't in the worksheet then the script gives and error. How can I avoid this?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Apple[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]CRITERIA[/TD]
[TD][/TD]
[TD]tot[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Orange[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]

109​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Drink[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]orange[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Banana[/TD]
[TD]

13​

[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD]

109​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]CHICKEN[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]sugar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Oil[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]Salt[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]sugar[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]BARS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Salt[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD]Drink[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]BARS[/TD]
[TD]

45​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


TRY

F2=SUMPRODUCT(SUMIF($A$1:$A$10000,D2:D10000,B1:B10000))

OR

F4=SUMPRODUCT(ISNUMBER(MATCH($A$1:$A$1000,D2:D1000,0))*(B1:B1000))