Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Q: IIF Statement
Hi,
Sheet 1 column A is alphanumeric, B is text and C is price, Sheet 2 has the same condition. In sheet 3 I'd like to check Sheet1 ColA and Sheet2 ColA if is equal then Store alphanumeric data in Sheet3 ColA and Sum Price (sheet1 colC + sheet2 colC). Pls note that there're over 1000 entries in each sheet and data in sheet1 ColA Row 10 is not necessarily equal to sheet2 ColA Row10 but may it be in Row150. How can it be done using IIF Statement? TIA Edu |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Q: IIF Statement
My thoughts would be to copy n paste Sheet2's data below Sheet1's (assumed
identically structured), then pivot on the combined source, placing col A's header into ROW area, "Price" into DATA area (SUM). Just a couple of seconds worth of effort, and it should yield the desired results. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "Edu07" wrote: Sheet 1 column A is alphanumeric, B is text and C is price, Sheet 2 has the same condition. In sheet 3 I'd like to check Sheet1 ColA and Sheet2 ColA if is equal then Store alphanumeric data in Sheet3 ColA and Sum Price (sheet1 colC + sheet2 colC). Pls note that there're over 1000 entries in each sheet and data in sheet1 ColA Row 10 is not necessarily equal to sheet2 ColA Row10 but may it be in Row150. How can it be done using IIF Statement? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Q: IIF Statement
Max,
I dont think it would work because as he said, sheet1 ColA and sheet2 ColA have the same data but not necessarily located in the same row. Sheet3 should scan Sheet1 ColA and sheet2 ColA for a match and if exists storing it in sheet3 summing ColC of the match. Im not sure Excel can do this. Cheers, Emilio "Max" escribió en el mensaje de noticias ... My thoughts would be to copy n paste Sheet2's data below Sheet1's (assumed identically structured), then pivot on the combined source, placing col A's header into ROW area, "Price" into DATA area (SUM). Just a couple of seconds worth of effort, and it should yield the desired results. -- Max Singapore |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Q: IIF Statement
I'm not sure. Best to have the OP's feedback to the thoughts. Anyway,
assuming your angle, it's always possible to do a backtest using the pivot o/p. Assuming the earlier pivot is now drawn from a combined source in a new sheet, and the data for col A's uniques appears in A5 down, sums in B5 down This backtest could be placed in C5: =IF(AND(COUNTIF(Sheet1!A:A,A5),COUNTIF(Sheet2!A:A, A5)),B5,"") with C5 copied down. An autofilter on col C could then be applied, and "(Non blanks)" chosen to derive the result -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "Emilio" wrote: Max, I dont think it would work because as he said, sheet1 ColA and sheet2 ColA have the same data but not necessarily located in the same row. Sheet3 should scan Sheet1 ColA and sheet2 ColA for a match and if exists storing it in sheet3 summing ColC of the match. Im not sure Excel can do this. Cheers, Emilio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |