How To Count Country For Each Product

Mar 24, 2014

I have a very big Excel table with many products. A product like a "pen" can have different attributes, then each of it gets an own row. Each row has one entry in the column "country". It indicates in which country the product gets shipped.

I want to count the country, in which the products get shipped.

But I don't want to count two times if a product "pen - blue" and "pen - yellow" both get shipped to the US.

I tried it to achieve this with a pivot table but it counts every row and I haven't found a way that it only counts each country per product once.

I have an simpliefied example attached. The columns "product" and "country" exist already, the column "country count" I hope to get automatically.!

Country
Product
Country count
Attribute

US

Pen
2
Blue

[Code] .......

View 6 Replies


ADVERTISEMENT

Count Cells Based On Country

Dec 6, 2013

Find attached Sample sheet : Sample Data.xlsx‎

View 2 Replies View Related

Add ‘country Name’ Column To Worksheet From A List Of Country Codes.

Apr 2, 2009

I've got a worksheet with five columns of data. One of the columns contains country codes. I have a list of countries that the codes relate to. I would like a new column in the worksheet that contains the country name. I'm not sure if this is possible, but it seem that most things are with VBA! The countries and their codes are on 'Sheet2' I relalise I might have to do some boring re-ordering of the data on 'Sheet2'

View 2 Replies View Related

Excel Count Function - Calculate How Many Yes A Country Receives?

Mar 7, 2013

I have a column which contains a list of countries and the next column is filled with Yes and No's.

Is there a formula which calculates how many Yes a country receives?

e.g. USA
Yes

Germany
No

France
No

[Code] ..........

View 2 Replies View Related

Formula - Week Product Level Count

Jan 5, 2014

How to solve my problem in attached file : Week Product Level Count.xlsx

View 1 Replies View Related

Sum Product To Count Data In A Date List

Feb 23, 2010

I have been using the sumproduct formula to count how many times a certain piece of data appears...which works fine...for text purposes such as names, initials etc...

I now need to amend the formula to count how many times a date appears in a range of dates.

I know when you use the sumproduct...dates must be input and formatted as text.

example

A1
01-Jan
02-Jan
5-Feb
7-Feb
12-Feb

formula to only count dates with feb in?

View 12 Replies View Related

Sumproduct Formula: Count The Components Used To Make One Product

Oct 7, 2008

Is there a simpler way to to the calculations on the spreedsheet i have attached?

Basically I want to count up the components used to make one product

View 14 Replies View Related

How To Count Every 5th Occurrence Of Product Code In Inconsistent List

Aug 31, 2012

I need to identify every 5th occurence of a product code (In column B)

the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51

So I would need to identify the one in Row 51 in the above example,

WorksOrderNumber
Product Code
DateEntered
Column1

56
80100811
01/08/2012 00:00

[code]......

View 5 Replies View Related

Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

View 2 Replies View Related

Dynamic Named Range For The Product Name On The Product Details Sheet

Dec 24, 2008

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

View 5 Replies View Related

Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

View 4 Replies View Related

Product Look Up: Look Up A Product Based On The Number And The Qty

Feb 20, 2009

i need to look up a product based on the number and the qty. see the example attached.

On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.

View 4 Replies View Related

Type The Product Id In The A Column Of The Orders Sheet(Sheet2) To Auto Insert The Product Name In The B Column

Jul 16, 2006

In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.

View 2 Replies View Related

Cell Referencing: Enter A Product Name In One Cell And Have The Price For That Product Automatically Appear In Another Cell

Feb 3, 2009

I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?

View 2 Replies View Related

Find Country Name Into String?

May 6, 2014

i am trying to find country name in cell consisting of sting, i applied "=INDEX(K2:X2,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(K2:X2,A2)),0),0))" this formula i am getting desired result but i am not able to apply ctrl+d since all cell reference are iterating.

View 8 Replies View Related

Conditional Average For Each Country

Mar 16, 2007

I have countries in column A and the values in column B.

I want to see the avareges of values for each country.

I have tried a few samples that I have found in the forum but they do not work for me.

View 9 Replies View Related

Country Code Lookup

Feb 23, 2009

We get a very detailed Call Data Records(CDR) for all telephone calls made by our employees. CDR typically contains the called number, duration in seconds and cost. What is missing it the city name and country name for the called number.

For e.g. a typical CDR log would be
Called # duration(secs) Cost
442075000000 40 0.02

From looking at this number, the country code is UK - 44 and the city code is 20 which is London.

I have a spreadsheet that contains Country code and city code for all major cities in the world.

Is it possible to create a lookup function, that would take the 'Called #' from the CDR and lookup the data in the CountryCity code spreadsheet and give me the called City and Contry name.

View 9 Replies View Related

Data Validation Of Country And City

Aug 5, 2014

Data validation using named ranges as countries are listed multiple times and to relate country with cities.

How can i relate country with city in two different columns(named ranges) not in order.

Formula to be used for Data Validation would be useful.

Sample data file is attached.

Its just a sample.data can be for 250 rows.

Book1.xlsx‎

View 2 Replies View Related

Search Country Name With Caller Number?

Mar 23, 2014

I have 2 excel sheets format is below mention. In sheet one i have some number with ISD code and in second sheet i have country name and Code. now i want to match Country name in sheet one but my problem is that there are some code are in 1 digit and some code are in 2,3,4,5 digits. If you see in example 220 is belong to Gambia and 224 belong to Guinea so first i want to match all code who is having 1 digit code second who is having 2 digit than 3 digit, 4 digit respectively.

If I'm using below mention formula it's give me first value from sheet to:

INDEX('Country Code'!C2:C229,MATCH(TRUE,INDEX(ISNUMBER(FIND('Country Code'!$B$2:$B$228,B4)),0),0))

As per Ist number 54911678222709 Formula will check Ist 5491 digit if it is Match than ok else chek 549 if it's match than ok else check 54 and 5.

Sheet One:-
Caller Number
54911678222709
5493426121222
971501364886
9715013648860
2207771650
22430919600
23190000368
23222273217

Sheet two as Country Code:-
Country CodeName
54Argentina
971Emiratos Arabes Unidos
220Gambia
224Guinea
231Liberia
232Sierra Leona

View 4 Replies View Related

How To Add Country Flags To Excel Table

Apr 11, 2013

how to add county flags to excel table.I have a table with list of countries and I would like to add picture of their flags into the same cell or to the neigboring cell.

View 1 Replies View Related

State And Country Check Macro

Jun 17, 2009

1 - If any cells (with data in it) in the "state" column contain any of the following abbreviations in it...

AB, BC, MB, PE, NB, NL, NS, NT, NU, ON, QC, SK, YT

Then all matching row cells in the column named "country" should say "Canada",

AND....(it must also meet this criteria)...

Criteria 2 - If any text in "Country" column is "Kryponite", AND "State" column is blank


If BOTH criteria 1&2 are met.....THEN...

then delete both the country & state columns

If not, keep both columns and highlight in light red any errors to this macro.

View 14 Replies View Related

Splitting Master Data Per Country Fails

Jan 3, 2014

I have a master data sheet with raw data in 19 columns and a variable amount of rows per week, but usually 50k+

The data contains entries from different Countries, and this I need to split up per country into different sheets.

Naming the different sheets with Country code like US, DK, UK, PL, etc...

Below code does that almost perfectly.

Only thing is that when there is no more data to split up, it creates the last sheet called "SheetXX" and since it can't name the sheet "nothing", I just get an error - Run-time error 1004 Method 'Name' of object'_worksheet' failed with the possibility to END or DEBUG. When going into DEBUG this line is highlighted. (wsNew.Name = c.Value)

I can just hit END and then proceed running my remaining macros, but I would prefer it not giving me an error in the first place. (My end goal is that all my macros (10ea) are run by the click of one button, and that is currently not possible, when this gives me an error).

I have tried the "Application.DisplayAlerts = False" without any luck, and also "On Error resume Next", maybe I have used it incorrectly. Do I have to search for a different way to split the data..??

VB:
Sub Split_data_pr_Country()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = sheets("Template")

[Code] .....

View 9 Replies View Related

Control That Report Sent Everyday Except Weekend For Each Country

Jun 29, 2014

I have a list of reports for many countries, I need to make sure each report is there for every country, except for weekend days.

report check.xlsx

View 1 Replies View Related

Determine If A Country On List Has Been Entered In A Cell?

Apr 18, 2013

I have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.

View 3 Replies View Related

Finding Country Based On Phone Numbers?

Dec 5, 2008

I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

View 9 Replies View Related

Finding Country Based On Phone Numbers

Dec 5, 2008

I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

View 9 Replies View Related

Currency Format To Change Based On Country

Feb 26, 2008

I am struggeling to format a column to display currency values formated with a different currency mark than the local system currency. Right now, when I run the macro on my machine I get my local currency (Israely Shekel), and on the Client's machine I get Euro. (I need it to be in dollars)

View 6 Replies View Related

Translate Data Of Dialed Numbers Into A Country Or Destination Name

Dec 21, 2008

I have some data for international dialed numbers, I need to translate this into a country or destination name.

for example if the dialled number begins with 001, the is the united states etc..
I have all of the required codes, but they all differ in length.
ie 0033 is spain but 003356 is a spain mobile.

I was thinking that i could use VLOOKUP to find the relevant text to display, but this does not work with the varying lengths of the codes.

View 9 Replies View Related

Excel 2003 :: Run Macro And Fill Out Cells When Country Is Selected?

Jun 8, 2014

I have a dropdownmanu in sheet1 with different countries taken from Column A in Sheet3. I need a macro to run when i select a country example Denmark. It will fill out transmittal code and also country code in named cells for it in sheet1. Info taken from Sheet3

Transmittal code is in below testsheet in Sheet1 cell E12.

Country code is in Sheet1 cell.

But this have to be possible to change. Also the range for the country have to be possible to change.

This vba code i need to run as soon as i select a country in the dropdown manu.

But one thing i would like to solve also is. When i select a country it will create a dropdownlist in I13 taken from the info in column, i have in Sheet3 column F. So if i select example Denmark, it will show a dropdown menu in sheet1 I13, with the ledger codes 10 and 6x. I have tried to make this work but cant make it work good.

I use excel 2003. Please have a look and upload the testsheet back.

View 14 Replies View Related

Extract Information From Closed Workbook Representing Data From A Country

May 22, 2013

I'm having a problem with getting a result from another workbook. The purpose is to extract information from a closed workbook representing data from a country (ie US.xlsm, UK.xlsm) into the open one (summary). I want to count the number of results in a column which are above a certain amount (in this case 100).

So this is my coding for getting this result

=SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))

Which works no problem for getting the number of results in the E column above 100. my issue is that I want to be able to change the name of the country in a cell, and then get the results automatically from the relevant workbook. I've been using CONCATENATE for these purposes and EVAL (from MOREFUNC)... and I get #REF!

So if I split up the above coding into two cells "=" and "SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))" (just to keep it simple) and then use CONCATENATE to combine them in a cell. Using EVAL on that cell gives me #REF!

I've tested SUMPRODUCT with EVAL and CONCATENATE on formulae like the one above which do not link to other workbooks, and they seem fine. and SUMPRODUCT and linking to a workbook also works fine in the first example.

So the problem is somehow coming from the combination of SUMPRODUCT, EVAL and accessing an external sheet.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved