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 |
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? |
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 |
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 |
All times are GMT +1. The time now is 06:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com