Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using offset to align data?
I have a spreadsheet containing thousands of accounts, and each account is
listed multiple times with various market data. Each row has the account name and a piece of market data. What I am trying to do is calculate the Market data for the "OUT" part of each account. Now i realize if I can combine "OUT"&Account&OurPruduct$ for each account I can perform a vlookup of the OUT market data. The problem is, how would I be able to place an "OUT" next to only the cells that fall under the "OUT" sub-heading. There are three sub-headings: OUT, IN, BETWEEN. For example: Account Company1 OUT Company1 OurProduct$ Company1 TotalMarket$ Company1 Share% Company1 IN Company1 OurProduct$ Company1 TotalMarket$ Company1 Share% Company1 Between Spreadsheet contains thousands of companies, and would like to find out market data for the OUT component of each company. Any suggestions? Thanks for your help in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using offset to align data?
The formula below, starting in row 2, and assuming that account is in
column A and the OUT, IN, Between etc data are in column B =IF(UPPER(B2)="OUT","OUT",IF(AND(OFFSET(B2,-1,1)="OUT",UPPER(B2)<"IN",UPPER(B2)<"BETWEEN")," OUT","")) results in... Company1......OUT.................OUT Company1......OurProduct$.....OUT Company1......TotalMarket$....OUT Company1......Share%............OUT Company1......IN.................... Company1......OurProduct$.... Company1......TotalMarket$... Company1......Share%.......... Company1......Between......... Company1......OurProduct$.... Company1......TotalMarket$... Company1......Share%.......... Company2......OUT...............OUT Company2......OurProduct$...OUT Company2......TotalMarket$...OUT Company2......Share%..........OUT Company2......IN................... Company2......OurProduct$.... Company2......TotalMarket$.... Company2......Share%.......... Company2......Between......... Is this what you are after? Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inconsistent Sorting | Excel Discussion (Misc queries) | |||
macro | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions |