Look up column name, match rows (a/cnumber) & summing up....its confusing!

Hi All,
Here is what I'v been trying to do but..!
I'v 2 sets of data (data1 & data2) Column name may be sane/different in
each data set and same applies to acct_no..  I want to prepare a report
that combine product & accounts data show accumulatd result on another
sheet/file.


Data 1 - upto Jan 2006

Acct. No	Total	Prod - A	Prod - B	Prod-C
1001	51	10	30	11
1002	47	15	20	12
1003	80	20	15	45
1004	64	25	16	23
Total	242	70	81	91

Data 2 - Feb 2006

Acct. No	Total	Prod - A	Prod - B
1002	7	5	2
1004	16	10	6
1009	9	3	6
Total	32	18	14

Report required

Acct   upto Jan'06    Prod - A   upto Jan'06	Prod - B   upto Jan'06-
Prod-C
             Prod-A                        Prod-B
Prod-C
1001	10		30	0	11	           0
1002	15	5	20	2	12                        0
1003	20		15	0	45                        0
1004	25	10	16	6	23                        0
1009	0	3	0	6	0	            0
Total	70	18	81	14	91	            0

Above is the report required..Data from Data 1 for Product A (upto
Jan'06) appear in one column according to corresponding account number
and beside this Current months data (feb 2006) from Data2 is listed.
Report automatically match column name (very next to it) and shows data
of prior month in respective account row. Here in "Upto Jan'06
Product-A" column shows 0 for account # 1009 as there was no data for
this column in Data1 set.

Hope I m clear .!!!!
thanks for your support & assistance..
Syed

0
3/6/2006 5:54:05 PM
excel 39879 articles. 2 followers. Follow

1 Replies
432 Views

Similar Articles

[PageSpeed] 35

You could use a pivot table and use multiple consolidation ranges.
It's sometimes difficult to work with, but is a quick way to marry up
information from different sources.

0
kletcho (34)
3/9/2006 5:41:38 AM
Reply:

Similar Artilces:

Column chart
I'm developing a chart for a client who has very specific requirements. The data is of the following type of info: Q1 2009 Q2 2009 Q3 2009 Q4 2009 YTD 2009 Alpha 3.3% 53.3% 18.8% 9.5% 105.8% Bravo -0.2% 12.2% 19.7% -22.5% 3.9% Charlie 106.6% 125.6% 2.3% -1.5% 368.4% Delta -8.3% 1.4% 0.9% 0.8% -8.1% I need to lay out the chart with the Y-axis being the % and the X-axis being the Quarters, etc. So far so good - I can do that. ...

Sheets changing names
The following macro saves various lists to new sheets in the same workbook. The first save is named 12-4-09 the second is 12-4-09a and so on. The initial Sheet is “Master” What happens is that on the first save the Master’s list does not remain as it was originally but changes to match the list that was just saved as (12-4-09) list This means that both sheets are the same On the next save to (12-4-09a) it actually has the list that was in 12-4-09a and the new list is in the old 12-4-09. I don’t know if I am making my self clear but if you open a workbook and rename the sheet Mas...

SUM rows in column if cell in different column is empty
Hi, I have a spreadsheet that has many rows of data. I'm trying to get a total for a column ("G") in a cell ("A2") but only on rows where the cell in another column ("J") for that row is empty.I tried SUMIF and couldn't get it to work. Can this be done with some kind of formula? Any help would be appreciated. Thanks, gg =SUMIF(I:I,"",G:G) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) <nospam@iamtheonewho.com> wrote in message news:1173743011.019953.66950@v33g2000cwv.googlegroup...

Check 2 columns of names for duplicates
I have 2 very large lists of names. I need to quickly see if any of the names that appear in one column match any of the names in the other column. (One list is existing customers, one list is leads for new business. I don't want to send existing customers an offer to do business.) Chip Pearson has a bunch of techniques for working with duplicates at: http://www.cpearson.com/excel/duplicat.htm jimp wrote: > > I have 2 very large lists of names. I need to quickly see if any of the names that appear in one column match any of the names in the other column. (One list is existing cust...

How do I include a date column in the Axis in Excel?
I'm creating a graph from a series of values in one column. In the adjacent column I have the date the value is entered. How can I get these dates to appear on the axis of the graph? On May 16, 4:54=A0pm, Greeneliza <Greenel...@discussions.microsoft.com> wrote: > I'm creating a graph from a series of values in one column. =A0In the adj= acent > column I have the date the value is entered. =A0How can I get these dates= to > appear on the axis of the graph? Please go to:- http://www.pierrefondes.com/ Item number 52. Pull up the Workbook on the sc...

Outlook Does Not Recognize One or More Names!
Using Microsoft Office 2003, all the latest updates. I get that error message now, when I click "Send" on an e-mail message to someone who does not appear in my address book, or to whom I did not send mail to or receive mail from before, and the mail will not go into the Outbox, to be sent. What is causing that? Of course I will sometimes write to people whom are not in my address book, and with whom I have not corresponded before. Of course Outlook will not always recognize an e-mail address I am sending a message to. Why that should generate an error message, and refuse to send t...

formatting color based on source column
Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not ...

converting excel rows into text
How can I take a row of data from a spreadsheet and convert it into text separated by commas in a Word document? I tried "saving as" but it didn't work as I expected it to. Thanks! Try saving the sheet as a .csv (File>Save As>Save as type: CSV) Then open the .csv in Word -- Max Singapore http://savefile.com/projects/236895 Downloads:16,000 Files:354 Subscribers:53 xdemechanik --- "mediamom" wrote: > How can I take a row of data from a spreadsheet and convert it into text > separated by commas in a Word document? I tried "saving as" but it ...

Exceeding 65,000 rows
Please help. I have a macro that pulls in a text file. This file has over 65,000 rows. The data pulled fills down sheet 1 an then once 65,000 rows is reeached a new sheet is inserted. Once this new sheet is inserted how do i get the macro to activate thi new sheet, and continue to fill in the data starting from cell A again. please hel -- moglione ----------------------------------------------------------------------- moglione1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2641 View this thread: http://www.excelforum.com/showthread.php?threadid=46749 ...

Sum
Since I don't always know how many rows I will have in a spreadsheet, I would like a macro that if I run the macros on a specific column, it automatically takes me to the end the filled column and drop down one more row to a blank cell and adds the entire column. My macro is cell specific using recorder and I don't want that. Thanks much for assistance! sub sumtoend() ac=activecell.column lr=cells(rows.count,ac).end(xlup).row cells(lr+2,ac)=application.sum(range(cells(1,ac),cells(lr,ac)) end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguill...

sum vlookup results
Dear friends: I have the following array of values in A1:A10 0 0 400 0 1000 0 0 1950 0 I have the following table in B1:C5: 1 10 501 20 1001 30 2001 40 3001 50 I am trying to find a formula that will sum the values returned if you were to take each value in the array A1:A10 and put them in the vlookup function separately using the table in B1:C5. The desired value for the above data would be = 60 (10+20+30, corresponding to the vlookup of 400, 1000, and 1950) Any help would be much appreciated! hi, you had two responses on this topic to your first post, do you see those ? -- isabelle...

Auto fill multiple sections with different names
I have weekly time broken down by category, however, only the total line has the corresponding names. How do i fill the above cells with the corresponding name on the total line. For example(where blank insert last name on total line) Col A Col D blank wk27 blank wk28 blank wk29 blank wk30 Smith Total blank wk27 blank wk28 blank wk29 johnson Total thanks Try this: 1. Select the cells in column A 2. Press F5 3. Select 'Blanks...

copy multiple rows
how can i copy or print multiple rows with a common value on a column... data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "Dianna" <Dianna@discussions.microsoft.com> wrote in message news:AA803F3F-A65C-4866-97D4-DCEC0B84F5AE@microsoft.com... > how can i copy or print multiple rows with a common value on a column... Dianna Try Data>Filter>Autofilter. Filter on the common value in the column. Copy and paste what is visble. To print, filter then print. The hidden rows won't print. Gord Dibben Excel MVP On Mon, 8 Nov 2004 08:4...

sum of only green cells
How do I sum e.g. only green cells in my worksheet, when I color the cells in two different colors, green and red? Hi see: http://www.xldynamic.com/source/xld.ColourCounter.html and http://www.cpearson.com/excel/colors.htm not possible without VBA -- Regards Frank Kabel Frankfurt, Germany Jan Caesar wrote: > How do I sum e.g. only green cells in my worksheet, when I color the > cells in two different colors, green and red? try Sub addcolorcells() For Each c In Selection If c.Interior.ColorIndex = 4 Then ms = ms + c Next MsgBox ms End Sub -- Don Guillett SalesAid Software donald...

Label columns in column charts
Now, it's Easter time, but I still need to work to complete my thesis...well, let's go on...:-) I've got a column chart in which the height of each column represents the sum of unique values, so that if the column's height is 4, it means it represents the occurence of 4 single events (1+1+1+1) Now, referring this example I wish to label each column placing the name of the single events, so, if the column's height is 4 the labels should be from 0 to 1 -> e1 from 1 to 2 -> e2 from 2 to 3 -> e3 from 3 to 4 -> e4 (note that I have the labels in my active sheet)...

Find and Delete Rows
I have about 4700 rows I need to delete. I can find them, but I don't know how to mass delete the rows. How can I use Find/Replace to delete the rows in the results of my find? You can't use Find/Replace to delete rows directly. However, you can use that feature to replace the text with a specific character or string (ie the replace would be something like ZZZDelete). Then you can sort the entire dataset and all of the rows to be deleted will be grouped together. Select all of them and delete. "Carlton A. Barlow" <CarltonABarlow@discussions.microsoft.com> wr...

Take a look at the correction pack for Internet Explorer
--kyqdpbmjgtnwd Content-Type: multipart/related; boundary="rifvwxfbay"; type="multipart/alternative" --rifvwxfbay Content-Type: multipart/alternative; boundary="ownmvybapkzrry" --ownmvybapkzrry Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now t...

Column Lookup
wMicorsoft Office Excel 2003 I need to look up an answer from a column based on another column and place the answer in yet another column. See example Col A Col B Col C Col D Col E (the answer) Apple 20 Orange 5 35 Orange 10 Apple 35 5 Search Col C based on Col A and return answer to Col E from Col D place this on cell E2 aand fill it down =vlookup(a2,c$2:d$1000,2,0) "Russ" <Russ@discussions.microsoft.com> wrote in message news:D28DB918-8136-48A0-BF32-C3...

Running sum in query
I am using a query that sellects transactions by vendor... there are two fields [Credits] and [Debits] These are sorted by date... I would like to have and expression that makes a running total of these two fields.. I would then like to us this expression in a report.. Thanks in Advance for your help Bob http://tinyurl.com/2r8z4w "Bob" <Bob@discussions.microsoft.com> wrote in message news:E78171A3-F215-423E-BF06-63D22822896F@microsoft.com... >I am using a query that sellects transactions by vendor... there are two > fields [Credits] and [Debits] These are sorted...

AJAX
Hello, Does anyone know how I can freeze the header row in the DataGrid that uses AJAX with IE8 Browser. I've tried applying css but it does not work in IE8 unless I switch the compatibility mode on. (which i can't on 100+ users). Thanks ...

Look at these security update
--wpxhtpbmrmsmmz Content-Type: multipart/related; boundary="gpfmogvvgagrxl"; type="multipart/alternative" --gpfmogvvgagrxl Content-Type: multipart/alternative; boundary="uxubtxafyzmt" --uxubtxafyzmt Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install ...

how do I reveerse name order last name, first name to first name, last name
I have a long database, 27k with names that I want to mail to. The list is last name, first name. I want to do a global change to first name, last name. I also want to add "Mr" to it. For example: the list now is Smith, John. I want to change it to Mr. John Smith for the entire list. Assuming there is only one comma in each: ="Mr. "&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))&" "&LEFT(A1,FIND(",",A1)-1) Note that some of the women might be offended.... 27k names, all male? Scott Don Smith wrote: > I hav...

Displayed Name and Email Address
Hi, Our company email account is actually a distribution group in the exchange server of our SBS2003. There are two members in this group. In other words, any inbound email to the company email account, both members (accounts) get an email copy. The emails of these two account are retrieved via outlook 2003 (which the type of the account is exchange server). Now the question is: how can I set the outlook so that the recipient "see" the email address is the distribution group address and name. This can easily be done in pop3 account. But for exchange server account, there is n...

Maximum Rows and Columns
Just got my new Office 2007 Suite of applications. One reason I wanted the new Office is that I work with large data files and the capability of 1 million rows and a lot of columns will help me. Unfortunately, I just tried to combine 2 large worksheets that totalled more than 66,000 rows and got the error message that I had exceeded the row maximum. How do I "turn on" the larger row and column capability? Any help would be much appreciated. Thanks. -- PBear You probably opened older format Excel files. Office button>Save as... choose Excel workbook (or macro enables w...

Changing Server Name
I set up an Exchange 2003 server. While messing around with the settings, I realized I did not like the name of the machine, so I changed it. But, in ESM, it still shows the old server name. How do I change the server name in ESM? Thanks, Jeff On Sat, 25 Mar 2006 14:32:51 -0800, Jeff Grossman <jeff.nospam@stikman.com> wrote: >I set up an Exchange 2003 server. While messing around with the >settings, I realized I did not like the name of the machine, so I >changed it. But, in ESM, it still shows the old server name. How do >I change the server name in ESM? > >Th...