PDA

View Full Version : Solved: Mapping cells from column to column with UID?



Chuckita
02-04-2008, 01:50 PM
I have a spreadsheet that is populated by a web query. In this sheet I have two columns that are NOT populated by the query, but are instead populated with manually-entered information that corresponds to the query-populated cells in that row.

The problem is that when the web query is refreshed, what was once on row 1 might now be on row 45, and the manually-populated data does not get moved to the new row with the corresponding cell. :help

How can I efficiently map the data to the appropriate cell? It may be of value to note that column A contains a unique ID for each row.

TIA! :bow:

Cheers,
Chuckita

anandbohra
02-05-2008, 01:05 AM
as u told column A contains a unique ID for each row instead of putting manual data u can get it by following functions like Vlookup, Index Match, sumproduct, sumif etc etc depends upon your requirement.

& make it practise to post your workbook with suitable example as its always helps to provide solution better.

Chuckita
02-05-2008, 10:45 AM
as u told column A contains a unique ID for each row instead of putting manual data u can get it by following functions like Vlookup, Index Match, sumproduct, sumif etc etc depends upon your requirement.

& make it practise to post your workbook with suitable example as its always helps to provide solution better.

Thanks for your reply Anandbohra. I attached a sample of my workbook, but you won't be able to pull the web query. Let me explain again the scenario: When the workbook is updated, columns A:I will automatically be updated to reflect the current contents of the database. Because the rows are ordered by the "Status" column in the query, for instance, an issue might move from the top of the spreadsheet to the bottom when it is changed from "Open" to "Closed", but the Notes & Status in columns J:K will remain on their current row instead of moving with the corresponding issue.

You can see that column A will always contain a unique Id.

Thanks again!!
Cheers, :hi:
Chuckita

anandbohra
02-05-2008, 10:11 PM
what u want in column J:K put that in new sheet made by me named master along with the unique code.

now in column j (Sheet Detail) use this code to get data
=VLOOKUP(A2,Master!$A:$C,2,0)
& this one in column k
=VLOOKUP(A2,Master!$A:$C,3,0)


attach file will clear my explaination

Bob Phillips
02-06-2008, 03:15 AM
It would seem to me that after the query you should re-create column J, but I cannot see the rule that determines that status.

Chuckita
02-06-2008, 09:42 AM
what u want in column J:K put that in new sheet made by me named master along with the unique code.

now in column j (Sheet Detail) use this code to get data
=VLOOKUP(A2,Master!$A:$C,2,0)
& this one in column k
=VLOOKUP(A2,Master!$A:$C,3,0)


attach file will clear my explaination

Thank you so much Anandbohra!!! Your solution works perfectly! This is just what I needed.
Cheers! :beerchug: